-- This scripts contains following function's definition:
-- =============================================================================
-- months_between(timestamptz,timestamptz)
-- sys_guid()
-- wm_concat(text)
-- nullif(anyelement,anyelement)
-- nvl2(anyelement,anyelement,anyelement)
-- nvl2(text,text,text)
-- nvl2(numeric,numeric,numeric)
-- nvl2(timestamptz,timestamptz,timestamptz)
-- trunc(timestamp,text)
-- trunc(timestamptz,text)
-- nanvl(numeric,numeric)
-- regexp_substr(text,text,int8)
-- regexp_substr(text,text,int8,int8,text,int8)
-- regexp_instr(text,text,int8,int8,int8,text,int8)
-- regexp_replace(text,text,text,int8,int8,text)
-- regexp_count(text,text,int8,text)
-- regexp_count(text,text,int8)
-- regexp_count(text,text)
-- replace(text,text)
-- ora_hash(anyelement,int8,int8)
-- show(text)
-- show_parameter(text)
-- dump(anyelement,numeric,int8,int8)
-- instrb(text,text,int8,int8)
-- vsize(anyelement)
-- tz_offset(text)
-- sys_context(text,text,int8)
-- userenv(text)
-- round(timestamptz,text)
-- bitor(int8,int8)
-- bitxor(int8,int8)
-- bit_and_agg(numeric)
-- bit_or_agg(numeric)
-- bit_xor_agg(numeric)
-- bin_to_num(int4[])
-- any_value(anyelement)
-- sinh(numeric)
-- cosh(numeric)
-- tanh(numeric)
-- lnnvl(bool)
-- numtoyminterval(numeric,text)
-- systimestamp()
-- remainder(numeric,numeric)
-- round_ties_to_even(numeric,int8)
-- soundex(text)
-- kurtosis_pop(numeric)
-- kurtosis_samp(numeric)
-- skewness_pop(numeric)
-- skewness_samp(numeric)
-- asciistr(text)
-- unistr(text)
-- to_blob(raw)
-- convert(text,text,text)
-- nls_charset_name(int8)
-- nls_charset_id(text)
-- empty_clob()
-- sys_extract_utc(timestamptz)
-- sys_extract_utc(timestamp)
-- new_time(timestamp,text,text)
-- lower(anyelement)
-- pg_catalog.substr(anyelement,int8)
-- pg_catalog.substr(anyelement,int8,int8)
-- add_months(timestamp,int8)
-- add_months(timestamptz,int8)
-- to_timestamp(text,text,text)
-- rawtohex(anyelement)
-- to_char(timestamp without time zone,text,text)
-- to_char(timestamptz,text,text)
-- to_lob(text)
-- =============================================================================


set client_min_messages='warning';
set time zone 'UTC';
set statement_timeout = 60000;
set behavior_compat_options = '';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    set client_min_messages='warning';
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        create schema compat_tools;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';

-- =============================================================================
-- check extension conflict function
-- =============================================================================
CREATE OR REPLACE FUNCTION compat_tools.pg_describe_object2(oid, oid, INTEGER) returns text
  LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
  $$
  DECLARE l_class_table text;
  l_object_name text;
  l_name_col    text;
  l_schema_col  text;
  l_schema_name text;
BEGIN
  SELECT relname, a.attname
    INTO l_class_table, l_name_col
    FROM pg_class c, pg_attribute a
   WHERE c.oid = a.attrelid
     AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
     AND a.attnum = 1
     AND c.oid = $1 ;

  SELECT a.attname
    INTO l_schema_col
    FROM pg_class c, pg_attribute a
   WHERE c.oid = a.attrelid
     AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
     AND a.attnum = 2
     AND c.oid = $1
     AND attname NOT LIKE '%owner';

  EXECUTE 'select ' || l_name_col || ' from ' || l_class_table || ' where oid = :1'
    INTO l_object_name
    USING  $2;
  EXECUTE 'select pn.nspname from pg_namespace pn,' || l_class_table || ' ct where pn.oid=ct.' || l_schema_col || ' and ct.oid=:1'
    INTO l_schema_name
    USING $2;

   if l_class_table='pg_proc' then
    select function_name into l_object_name from  compat_tools.pg_function_list where oid=$2;
   end if;

    RETURN l_schema_name || '.' || l_object_name;
exception when others then return '';
END;
$$;

create or replace FUNCTION compat_tools.check_extension_conflict(text) RETURNS bool
  LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
  $$
  DECLARE
   l_exists int4;
BEGIN
  SELECT COUNT(1)
    INTO l_exists
    FROM pg_depend t, pg_extension e
   WHERE t.refobjid = e.oid
     AND lower(compat_tools.pg_describe_object2(classid, objid, objsubid)) = lower($1);

   IF l_exists > 0 THEN
     RETURN true;
   ELSE
     RETURN false;
   END IF;
END;
$$;

-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_function(varchar, varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_function( p_object_type    varchar(10)
                                                            , p_object_name    varchar(128)
                                                            , p_object_version varchar(10)
                                                            , p_function_lang  varchar(16)  default 'sql'
                                                            , p_object_schema  varchar(128) default 'pg_catalog')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean       := 'true';
    l_operation  varchar(128);
    l_depend_cnt bigint;
    l_app_name   varchar(128)  := current_setting('application_name');
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    );

    -- 字符串参数统一转小写
    p_object_name := lower(p_object_name);
    p_object_type := lower(p_object_type);
    p_object_schema := lower(p_object_schema);

    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_object_schema||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
    select max(language) into l_language
      from compat_tools.pg_function_list
     where schema_name = p_object_schema
       and function_name = p_object_name;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_object_schema||'.'||p_object_name;
    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif compat_tools.check_extension_conflict(p_object_schema||'.'||p_object_name)    -- 系统中已有extension创建同名对象
    then
        l_result='false';
        l_operation= 'Skip due to extension';
    elsif l_version is null
    then
        l_operation := 'Initial creation (Ver)';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_app_name != 'checkMe'
    then
        if l_result
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
                    else
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
                    end if;

                    insert into compat_tools.compat_version
                    values ( p_object_type
                           , p_object_schema||'.'||p_object_name
                           , p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 32);
            end;
        else
            insert into compat_tools.compat_version
            values ( p_object_type
                   , p_object_schema||'.'||p_object_name
                   , p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result values ( p_object_type
                                   , p_object_schema||'.'||p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Testing Table/Procedure
-- =============================================================================
create table if not exists compat_tools.compat_testing
(
    test_expr       text,      -- 测试表达式
    test_result     text,      -- 表达式结果
    expect_result   text,      -- 预期结果
    test_ok         bool,      -- 测试是否通过
    test_timestamp  timestamp  default now(), -- 测试时间
    constraint pk_compat_testing_expr primary key(test_expr)
);

drop procedure if exists compat_tools.f_unit_test(text, text, text, text);
create or replace procedure compat_tools.f_unit_test( p_test_expr     text
                                                    , p_expect        text
                                                    , p_compare_type  text  default '='
                                                    , p_db_compat     text  default NULL)
as
declare
    l_compat_valid bool    := 'true'::bool;
    l_error_code   text;
    l_error_mesg   text;
    l_test_result  text;
    l_test_ok      bool;
    l_app_name     varchar(128)  := current_setting('application_name');
begin
    if l_app_name != 'checkMe'
    then
        if p_db_compat is not null
        then
            select count(*)::bool
              into l_compat_valid
              from pg_database
             where datname = current_database()
               and datcompatibility = p_db_compat;
        end if;

        if l_compat_valid
        then
            execute immediate 'select '||p_test_expr||', cast('||p_test_expr||' as text) '||p_compare_type||' '||p_expect
               into l_test_result, l_test_ok;
            insert into compat_tools.compat_testing
            values (p_test_expr, l_test_result, p_compare_type||' '||p_expect, l_test_ok)
            on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                                  , expect_result = EXCLUDED.expect_result
                                  , test_ok = EXCLUDED.test_ok
                                  , test_timestamp = now();
        end if;
    end if;
exception
    when others then
        get stacked diagnostics l_error_code = returned_sqlstate, l_error_mesg = message_text;
        insert into compat_tools.compat_testing
        values (p_test_expr, l_error_code||': '||l_error_mesg, p_compare_type||' '||p_expect, l_error_code||': '||l_error_mesg = trim(p_expect, ''''))
        on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                              , expect_result = EXCLUDED.expect_result
                              , test_ok = EXCLUDED.test_ok
                              , test_timestamp = now();
end;
/



-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;
-- =========================================================================
-- 测试用例：
-- ----------
-- select compat_tools.f_unit_test('f_version_compare (''1.1.1'', ''2.0.0'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''2.1.1'', ''2.0.0'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3'')', '0');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.12.3'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3.4'', ''1.2.3'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3.4'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2...3'', ''1.2.3.4'')', 'NULL', 'IS');
-- select compat_tools.f_unit_test('f_version_compare (null, ''1.2.3.4'')', 'NULL', 'IS');
-- select * from compat_tools.compat_testing where test_expr like 'f_version_compare%';
-- =========================================================================



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
    l_result text;
    l_db_encoding text;
begin
    -- 获取当前DB的字符集编码
    select pg_encoding_to_char(encoding) into l_db_encoding from pg_database where datname =current_database();

    -- =========================================================================
    -- months_between(timestamptz,timestamptz)
    -- 2.1 版本修改返回类型 float8 -> numeric
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'months_between(timestamptz,timestamptz)', '2.1')
    then
        CREATE or replace FUNCTION pg_catalog.months_between( p_ts1 timestamptz
                                                            , p_ts2 timestamptz)
        RETURNS numeric IMMUTABLE strict AS $$
        -- SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer
        select (case when dd1 = dd2 or (m1 = dd1 and m2 = dd2)
                    then yd * 12 + mmd
                    else yd * 12 + mmd
                       + (dd1 - dd2) / 31
                       + hd / 31 / 24
                       + md / 31 / 24 / 60
                       + sd / 31 / 24 / 60 / 60
               end)::numeric
          from (select extract('day' from date_trunc('month', p_ts1) + interval '1 month -1 day') as m1
                     , extract('day' from date_trunc('month', p_ts2) + interval '1 month -1 day') as m2
                     , extract('year' from p_ts1) - extract('year' from p_ts2) as yd
                     , extract('month' from p_ts1) - extract('month' from p_ts2) as mmd
                     , extract('day' from p_ts1) as dd1
                     , extract('day' from p_ts2) as dd2
                     , extract('hour' from p_ts1) - extract('hour' from p_ts2) as hd
                     , extract('minute' from p_ts1) - extract('minute' from p_ts2) as md
                     , extract('second' from p_ts1) - extract('second' from p_ts2) as sd
               ) as x;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('months_between(''2004-03-31 12:12:12'', ''2004-02-29'')', '1') into l_result;
    select compat_tools.f_unit_test('round(months_between(''2004-03-30 12:12:12'', ''2004-02-29''),14)', '1.04866039426523') into l_result;
    select compat_tools.f_unit_test('months_between(''2004-03-29 12:12:12'', ''2004-02-29'')', '1') into l_result;
    select compat_tools.f_unit_test('round(months_between(''2004-06-01 12:12:12'', ''2004-04-30''),14)', '1.08091845878136') into l_result;
    select compat_tools.f_unit_test('round(months_between(''2004-05-01 12:12:12'', ''2004-03-31''),14)', '1.04866039426523') into l_result;
    select compat_tools.f_unit_test('round(months_between(''2014-12-01 12:12:12'', ''2004-03-31''),12)', '128.048660394265') into l_result;
    select compat_tools.f_unit_test('months_between(''2014-12-31 12:12:12'', ''2004-03-31'')', '129') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- sys_guid()
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'sys_guid()', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.sys_guid()
        RETURNS varchar
        AS $$
            select upper(md5(random()::text || clock_timestamp()::text));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('sys_guid()', 'NOT NULL', 'IS','A') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- wm_concat(text)
    -- =========================================================================
    -- 变更历史：
    --    2022-06-08  1.1  版本号增加，以便能创建 wmsys.wm_concat 同义词
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'wm_concat(text)', '1.1', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_state_func (results text, val text)
        RETURNS text
        LANGUAGE sql COST 50 IMMUTABLE
        AS $$ select results || ',' ||val; $$;

        CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_final_func (results text)
        RETURNS text
        LANGUAGE sql COST 111 IMMUTABLE
        AS $$ select substr(results, 2); $$;

        CREATE AGGREGATE pg_catalog.wm_concat(text)
        (
          sfunc = pg_catalog.vm_concat_state_func,
          stype = text,
          initcond = '',
          FINALFUNC = pg_catalog.vm_concat_final_func
        );

        -- 创建同义词 wmsys.wm_concat, 以便兼容 Oracle 中对应的用法
        select count(*) into l_cnt
          from pg_namespace
         where nspname = 'wmsys';
        if l_cnt = 0
        then
            create schema wmsys;
        end if;
        create or replace synonym wmsys.wm_concat for pg_catalog.wm_concat;
    end if;


    -- =========================================================================
    -- nullif(anyelement,anyelement)
    -- =========================================================================
    -- 备注： Oracle 本身就有，无需支持
    -- if compat_tools.drop_compat_function('function', 'nullif(anyelement,anyelement)', '1.0')
    -- then
    --     CREATE or replace FUNCTION pg_catalog.nullif(p_elem1 anyelement, p_elem2 anyelement)
    --     RETURNS anyelement
    --     AS $$
    --         select case when p_elem1 = p_elem2 then null else p_elem1 end;
    --     $$ LANGUAGE sql;
    -- end if;


    -- =========================================================================
    -- nvl2(anyelement,anyelement,anyelement)
    -- nvl2(text,text,text)
    -- nvl2(numeric,numeric,numeric)
    -- nvl2(timestamptz,timestamptz,timestamptz)
    -- =========================================================================
    --if exists  nvl2("any", anyelement, anyelement) and nvl2("any", text, text) then skip
    select count(1) into l_cnt from pg_proc where proname ='nvl2'
       and '"any"'::regtype = any(case when proallargtypes is null 
         then proargtypes else proallargtypes end); 
    if l_cnt<2 then

    if compat_tools.drop_compat_function('function', 'nvl2(anyelement,anyelement,anyelement)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nvl2(p_elem anyelement, p_elem_not_null anyelement, p_elem_null anyelement)
        RETURNS anyelement
        IMMUTABLE
        AS $$
            select case when p_elem is null then p_elem_null else p_elem_not_null end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'nvl2(text,text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nvl2(p_elem text, p_elem_not_null text, p_elem_null text)
        RETURNS text
        IMMUTABLE
        AS $$
            select case when p_elem is null then p_elem_null else p_elem_not_null end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'nvl2(numeric,numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nvl2(p_elem numeric, p_elem_not_null numeric, p_elem_null numeric)
        RETURNS numeric
        IMMUTABLE
        AS $$
            select case when p_elem is null then p_elem_null else p_elem_not_null end;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'nvl2(timestamptz,timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nvl2(p_elem timestamptz, p_elem_not_null timestamptz, p_elem_null timestamptz)
        RETURNS timestamptz
        IMMUTABLE
        AS $$
            select case when p_elem is null then p_elem_null else p_elem_not_null end;
        $$ LANGUAGE sql;
    end if;

    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('nvl2(1, 2, 3)', '2') into l_result;
    select compat_tools.f_unit_test('nvl2(null, 2, 3)', '3') into l_result;
    select compat_tools.f_unit_test('nvl2(1, null, 3)', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('nvl2(''asda''::text, ''x'', ''y'')', '''x''') into l_result;
    -- =========================================================================
    end if;


    -- =========================================================================
    -- trunc(timestamp,text)
    -- trunc(timestamptz,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'trunc(timestamp,text)', '1.1')
    then
        CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamp, p_fmt text)
        RETURNS timestamp without time zone
        IMMUTABLE
        AS $$
            select (case upper(p_fmt)
                    -- when '' then 'microseconds'
                    -- when '' then 'milliseconds'
                    when 'SS' then date_trunc('second', p_date)
                    WHEN 'MI' then date_trunc('minute', p_date)
                    WHEN 'HH' then date_trunc('hour', p_date)
                    WHEN 'HH12' then date_trunc('hour', p_date)
                    WHEN 'HH24' then date_trunc('hour', p_date)
                    when 'DAY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'D' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DDD' then date_trunc('day', p_date)
                    when 'DD' then date_trunc('day', p_date)
                    when 'J' then date_trunc('day', p_date)
                    when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1)  -- 历史上最近的与当月1号相同星期时间的时间
                    when 'IW' then date_trunc('week', p_date)  -- 一周的第一天（从周一开始）
                    when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1)  -- 历史上最近的与当年1号相同星期时间的时间
                    when 'MONTH' then date_trunc('month', p_date)
                    when 'MON' then date_trunc('month', p_date)
                    when 'MM' then date_trunc('month', p_date)
                    when 'RM' then date_trunc('month', p_date)
                    when 'Q' then date_trunc('quarter', p_date)
                    when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'SYYYY' then date_trunc('year', p_date)
                    when 'YYYY' then date_trunc('year', p_date)
                    when 'YEAR' then date_trunc('year', p_date)
                    when 'SYEAR' then date_trunc('year', p_date)
                    when 'YYY' then date_trunc('year', p_date)
                    when 'YY' then date_trunc('year', p_date)
                    when 'Y' then date_trunc('year', p_date)
                    -- when '' then 'decade'
                    when 'CC' then date_trunc('century', p_date)
                    when 'SCC' then date_trunc('century', p_date)
                    -- when '' then 'millennium'
                    else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'trunc(timestamptz,text)', '1.1')
    then
        CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamptz, p_fmt text)
        RETURNS timestamp without time zone
        IMMUTABLE
        AS $$
            select (case upper(p_fmt)
                    -- when '' then 'microseconds'
                    -- when '' then 'milliseconds'
                    when 'SS' then date_trunc('second', p_date)
                    WHEN 'MI' then date_trunc('minute', p_date)
                    WHEN 'HH' then date_trunc('hour', p_date)
                    WHEN 'HH12' then date_trunc('hour', p_date)
                    WHEN 'HH24' then date_trunc('hour', p_date)
                    when 'DAY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'D' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DDD' then date_trunc('day', p_date)
                    when 'DD' then date_trunc('day', p_date)
                    when 'J' then date_trunc('day', p_date)
                    when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1)  -- 历史上最近的与当月1号相同星期时间的时间
                    when 'IW' then date_trunc('week', p_date)  -- 一周的第一天（从周一开始）
                    when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1)  -- 历史上最近的与当年1号相同星期时间的时间
                    when 'MONTH' then date_trunc('month', p_date)
                    when 'MON' then date_trunc('month', p_date)
                    when 'MM' then date_trunc('month', p_date)
                    when 'RM' then date_trunc('month', p_date)
                    when 'Q' then date_trunc('quarter', p_date)
                    when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'SYYYY' then date_trunc('year', p_date)
                    when 'YYYY' then date_trunc('year', p_date)
                    when 'YEAR' then date_trunc('year', p_date)
                    when 'SYEAR' then date_trunc('year', p_date)
                    when 'YYY' then date_trunc('year', p_date)
                    when 'YY' then date_trunc('year', p_date)
                    when 'Y' then date_trunc('year', p_date)
                    -- when '' then 'decade'
                    when 'CC' then date_trunc('century', p_date)
                    when 'SCC' then date_trunc('century', p_date)
                    -- when '' then 'millennium'
                    else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    --     只针对 3.0.0 以下版本测试， 3.0.0 版本自带的 trunc 用法与 Oracle 不一致
    -- ----------
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '3.0.0') = -1
    then
        -- select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SS''   )', '''2012-12-12 12:12:12''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MI''   )', '''2012-12-12 12:12:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH''   )', '''2012-12-12 12:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH12'' )', '''2012-12-12 12:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''HH24'' )', '''2012-12-12 12:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DAY''  )', '''2012-12-09 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DY''   )', '''2012-12-09 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''D''    )', '''2012-12-09 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DDD''  )', '''2012-12-12 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''DD''   )', '''2012-12-12 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''J''    )', '''2012-12-12 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''W''    )', '''2012-12-08 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IW''   )', '''2012-12-10 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''WW''   )', '''2012-12-09 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MONTH'')', '''2012-12-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MON''  )', '''2012-12-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''MM''   )', '''2012-12-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''RM''   )', '''2012-12-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''Q''    )', '''2012-10-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IYYY'' )', '''2012-01-02 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IYY''  )', '''2012-01-02 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''IY''   )', '''2012-01-02 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''I''    )', '''2012-01-02 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SYYYY'')', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YYYY'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YEAR'' )', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SYEAR'')', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YYY''  )', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''YY''   )', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''Y''    )', '''2012-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''CC''   )', '''2001-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamp, ''SCC''  )', '''2001-01-01 00:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH''   )', '''2012-12-12 02:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH12'' )', '''2012-12-12 02:00:00''::timestamp') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamp, ''HH24'' )', '''2012-12-12 02:00:00''::timestamp') into l_result;

        -- select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SS''   )', '''2012-12-12 12:12:12''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MI''   )', '''2012-12-12 12:12:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH''   )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH12'' )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''HH24'' )', '''2012-12-12 12:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DAY''  )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DY''   )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''D''    )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DDD''  )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''DD''   )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''J''    )', '''2012-12-12 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''W''    )', '''2012-12-08 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IW''   )', '''2012-12-10 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''WW''   )', '''2012-12-09 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MONTH'')', '''2012-12-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MON''  )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''MM''   )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''RM''   )', '''2012-12-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''Q''    )', '''2012-10-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IYYY'' )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IYY''  )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''IY''   )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''I''    )', '''2012-01-02 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SYYYY'')', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YYYY'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YEAR'' )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SYEAR'')', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YYY''  )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''YY''   )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''Y''    )', '''2012-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''CC''   )', '''2001-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 12:12:12.123456''::timestamptz, ''SCC''  )', '''2001-01-01 00:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH''   )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH12'' )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
        select compat_tools.f_unit_test('trunc(''2012-12-12 02:12:12.123456''::timestamptz, ''HH24'' )', '''2012-12-12 02:00:00''::timestamptz') into l_result;
    end if;
    -- =========================================================================


    -- =========================================================================
    -- nanvl(numeric,numeric)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'nanvl(numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nanvl(p_num1 numeric, p_num2 numeric)
        RETURNS numeric
        IMMUTABLE
        AS $$
            select case when p_num1 = 'NaN' then p_num2 else p_num1 end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('nanvl(1,1)'   , '1') into l_result;
    select compat_tools.f_unit_test('nanvl(2,3)'   , '2') into l_result;
    select compat_tools.f_unit_test('nanvl(3,null)', '3') into l_result;
    select compat_tools.f_unit_test('nanvl(null,4)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- regexp_substr(text,text,int8)
    -- regexp_substr(text,text,int8,int8,text,int8)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- =========================================================================
    -- 变更历史：
    --   3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int4)', '3.0') into l_result;
    if compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int8)', '3.0')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source    text
                                                            , p_pattern   text
                                                            , p_position  int8)
        RETURNS text
        IMMUTABLE
        AS $$
            select regexp_substr(case when p_position > 1 then substr(p_source, p_position) else p_source end, p_pattern);
        $$ LANGUAGE sql;
    end if;
    SELECT compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int4,int4,text,int4)', '3.0', 'plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'regexp_substr(text,text,int8,int8,text,int8)', '3.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source     text
                                                            , p_pattern    text
                                                            , p_position   int8
                                                            , p_occurrence int8
                                                            , p_flag       text  default 'c'
                                                            , p_subexpr    int8  default 0)
        RETURNS text
        IMMUTABLE
        AS $$
        declare
            l_result    text;
        begin
            if p_position < 1
            then
                raise exception 'ERROR: Argument "p_position" must be greater than 0  (p_position [%] > 0)', p_position;
            elsif p_occurrence < 1
            then
                raise exception 'ERROR: Argument "p_occurrence" must be greater than 0 (p_occurrence [%] > 0)', p_occurrence;
            elsif p_subexpr < 0
            then
                raise exception 'ERROR: Argument "p_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', p_subexpr;
            end if;
            select max(match_array[p_subexpr + 1]) into l_result
              from (SELECT rownum as id
                         , regexp_matches( substr(p_source, p_position)
                                         , '('||p_pattern||')'
                                         , case lower(p_flag) when 'i' then 'gi' else 'g' end
                                         ) as match_array
                   ) as t
             where id = p_occurrence;

            return l_result;
        end;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 1)'   , '''abcd.AaBbCc''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*?c'', 1)'   , '''abc''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 3)'   , '''aBbCc''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''abcd.AaBbCcDd.1234567890'', ''a.*c'' , 8)'   , 'NULL', 'IS') into l_result;

    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, ''c'')'    , 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, ''i'')'    , '''bequebazil''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 1, ''i'')'    , '''barbeque''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 2, ''i'')'    , '''bazilbarf''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'')'    , '''barfbonk''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 0)' , '''barfbonk''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 1)' , '''barf''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 2)' , '''bonk''') into l_result;
    select compat_tools.f_unit_test('regexp_substr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, ''i'', 3)' , 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- regexp_instr(text,text,int8,int8,int8,text,int8)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- 注意： 最后一个参数 p_subexpr 暂时不支持，即不能确定匹配字符串中某个子串的位置
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'regexp_instr(text,text,int4,int4,int4,text,int4)', '2.0', 'plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'regexp_instr(text,text,int8,int8,int8,text,int8)', '2.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_instr ( p_source     text
                                                           , p_pattern    text
                                                           , p_position   int8
                                                           , p_occurrence int8
                                                           , p_return_opt int8  default 0
                                                           , p_flag       text  default 'c'
                                                           , p_subexpr    int8  default 0)
        RETURNS int
        IMMUTABLE
        AS $$
        declare
            l_result     int   := 0;
            i_position   int;
            i_occurrence int;
            i_return_opt int;
            i_subexpr    int;
        begin
            i_position   := p_position::int;
            i_occurrence := p_occurrence::int;
            i_return_opt := p_return_opt::int;
            i_subexpr    := p_subexpr::int;
            if i_position < 1
            then
                raise exception 'ERROR: Argument "i_position" must be greater than 0  (p_position [%] > 0)', i_position;
            elsif i_occurrence < 1
            then
                raise exception 'ERROR: Argument "i_occurrence" must be greater than 0 (p_occurrence [%] > 0)', i_occurrence;
            elsif i_return_opt < 0
            then
                raise exception 'ERROR: Argument "i_return_opt" must not be smaller than 0 (p_return_opt [%] >= 0)', i_return_opt;
            elsif i_subexpr < 0
            then
                raise exception 'ERROR: Argument "i_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', i_subexpr;
            end if;
            select case i_return_opt
                   -- i_return_opt = 0, 匹配字符串开头位置
                   when 0 then instr(repeat(' ', i_position - 1)
                                   ||pg_catalog.regexp_replace(source_str,
                                                               p_pattern,
                                                               signal_str||'\&'||signal_str,
                                                               case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                              ),
                                     signal_str,
                                     i_position ,
                                     i_occurrence * 2 - 1
                                    ) - (2 * (i_occurrence - 1)) * 3
                   -- i_return_opt != 0, 匹配字符串结束位置的下一个位置
                   else instr(repeat(' ', i_position - 1)
                            ||pg_catalog.regexp_replace(source_str,
                                                        p_pattern,
                                                        signal_str||'\&'||signal_str,
                                                        case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                       ),
                              signal_str,
                              i_position ,
                              i_occurrence * 2
                             ) - (2 * i_occurrence - 1) * 3
                   end into l_result
              from (select substr(p_source, i_position) as source_str
                         , chr(30)||chr(26)||chr(25) as signal_str) as t;
            return coalesce(l_result, 0);
        end;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 0, ''c'')'    ,  0 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 0, ''i'')'    ,  7 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 1, 1, ''i'')'    , 17 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 1, 0, ''i'')'    ,  4 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 1, 2, 0, ''i'')'    , 12 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'')'    , 17 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 0)' , 17 ) into l_result;
    select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 1)' , 17 ) into l_result;

    -- select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 2)' , 21 ) into l_result;  -- 不支持最后一个参数 p_subexpr
    -- select compat_tools.f_unit_test('regexp_instr(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', 5, 2, 0, ''i'', 3)' ,  0 ) into l_result;  -- 不支持最后一个参数 p_subexpr

    -- 以下测试用例在 openGauss 中与 Oracle 有差异：
    --   Oracle 返回第二个 Oracle 的位置： 8, 14, 21, 21
    --   openGauss 中由 instr 返回的是第一个满足的位置： 8, 8, 8, 8
    -- SELECT regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 0) as c1
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 1) as c1
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 2) as c2
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*Oracle.*(Oracle)', 1, 1, 0, 'i', 1) as c3
    --   from dual;

    -- SELECT regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 1, 0, 'i') as c1
    --      , regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 2, 0, 'i') as c2
    --      , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 1, 0, 'i') as c3
    --      , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 2, 0, 'i') as c4
    --   FROM DUAL;
    -- =========================================================================


    -- =========================================================================
    -- regexp_replace(text,text,text,int8,int8,text)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'regexp_replace(text,text,text,int4,int4,text)', '2.0', 'plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'regexp_replace(text,text,text,int8,int8,text)', '2.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_replace ( p_source      text
                                                             , p_pattern     text
                                                             , p_replacement text
                                                             , p_position    int8
                                                             , p_occurrence  int8  default 0
                                                             , p_flag        text  default 'c')
        RETURNS text
        IMMUTABLE
        AS $$
        declare
            l_result    text   := 0;
        begin
            if p_position < 1
            then
                raise exception 'ERROR: Argument "p_position" must be greater than 0  (p_position [%] > 0)', p_position;
            elsif p_occurrence < 0
            then
                raise exception 'ERROR: Argument "p_occurrence" must not be smaller than 0 (p_occurrence [%] >= 0)', p_occurrence;
            end if;

            if p_occurrence = 0
            then
                select pre_str
                    || regexp_replace(source_str,
                                      p_pattern,
                                      p_replacement,
                                      case lower(p_flag) when 'i' then 'gi' else 'g' end
                                     )
                  into l_result
                  from (select substr(p_source, 1, p_position - 1) as pre_str
                             , substr(p_source, p_position) as source_str
                             , chr(30)||chr(26)||chr(25) as signal_str) as t;
            else
                select string_agg(case when id % 3 = 2 and p_occurrence = round(id/3.0) then ''  -- 原始值的取舍
                                       when id % 3 = 0 and p_occurrence != round(id/3.0) then ''  -- 替换值的取舍
                                       else partial_str
                                  end,
                                  ''
                                 ) into l_result
                  from (select rownum as id
                             , unnest(string_to_array( pre_str
                                                    || regexp_replace(source_str,
                                                                      p_pattern,
                                                                      signal_str||'\&'||signal_str||p_replacement||signal_str,  -- 同时保留原字符串与替换后的字符串，以便在外层再按照 p_occurrence 进行选择
                                                                      case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                                     ),
                                                       signal_str
                                                     )
                                     ) as partial_str
                          from (select substr(p_source, 1, p_position - 1) as pre_str
                                     , substr(p_source, p_position) as source_str
                                     , chr(30)||chr(26)||chr(25) as signal_str) as t) as x;
            end if;
            return l_result;
        END;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 0, ''i'')'   , '''foobarXXXXXX''') into l_result;
    select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''#\1#'', 5, 0, ''i'')' , '''foobar#beque##barf#''') into l_result;
    select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 1, ''i'')'   , '''foobarXXXbarfbonk''') into l_result;
    select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 2, ''i'')'   , '''foobarbequebazilXXX''') into l_result;
    select compat_tools.f_unit_test('regexp_replace(''foobarbequebazilbarfbonk'', ''(b[^b]+)(B[^b]+)'', ''XXX'', 5, 3, ''i'')'   , '''foobarbequebazilbarfbonk''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- regexp_count(text,text,int8,text)
    -- regexp_count(text,text,int8)
    -- regexp_count(text,text)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- =========================================================================
    -- 变更历史：
    --   3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    --   4.0 : 改为无默认值，根据参数个数重载三个函数
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'regexp_count(text,text,int4,text)', '3.0', 'sql') into l_result;
    if compat_tools.drop_compat_function('function', 'regexp_count(text,text,int8,text)', '3.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source      text
                                                           , p_pattern     text
                                                           , p_position    int8
                                                           , p_flag        text)
        RETURNS int
        IMMUTABLE
        AS $$
        select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', case lower(p_flag) when 'i' then 'gi' else 'g' end))
             - length(source_str)
          from (select substr(p_source, p_position) as source_str) as x;
        $$ LANGUAGE sql;
    end if;

    if compat_tools.drop_compat_function('function', 'regexp_count(text,text,int8)', '3.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source      text
                                                           , p_pattern     text
                                                           , p_position    int8)
        RETURNS int
        IMMUTABLE
        AS $$
        select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', 'g'))
             - length(source_str)
          from (select substr(p_source, p_position) as source_str) as x;
        $$ LANGUAGE sql;
    end if;

    if compat_tools.drop_compat_function('function', 'regexp_count(text,text)', '3.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source      text
                                                           , p_pattern     text)
        RETURNS int
        IMMUTABLE
        AS $$
        select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', 'g'))
             - length(source_str)
          from (select substr(p_source, 1) as source_str) as x;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'')', 1) into l_result;
    select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 1, ''i'')', 2) into l_result;
    select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 3)', 0) into l_result;
    select compat_tools.f_unit_test('regexp_count(''abcdA123'', ''a.'', 3, ''i'')', 1) into l_result;
    -- =========================================================================


    -- =========================================================================
    -- replace(text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'replace(text,text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.replace ( p_source      text
                                                      , p_pattern     text)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.replace(p_source, p_pattern, '');
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('replace(''abcdAbcdasd'', ''a'')', '''bcdAbcdsd''') into l_result;
    select compat_tools.f_unit_test('replace(''abcdAbcdasd'', ''s'')', '''abcdAbcdad''') into l_result;
    select compat_tools.f_unit_test('replace(''abcdAbcdasd'', null)', '''abcdAbcdasd''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- ora_hash(anyelement,int8,int8)
    -- 测试用例：
    --   select ora_hash(relname, 10) from pg_class limit 10;
    --   select ora_hash(object_name, 10) from dba_objects where rownum < 11;
    -- =========================================================================
    -- 变更记录：
    --     v1.1 => 解决默认 p_buckets 参数为零时，除数为零的错误，以及桶数量少一个的问题
    --     v2.0 => 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'ora_hash(anyelement,int4,int4)', '2.0', 'plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'ora_hash(anyelement,int8,int8)', '2.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.ora_hash ( p_data     anyelement
                                                       , p_buckets  int8        default 0
                                                       , p_seed     int8        default 0)
        RETURNS bigint
        AS $$
        declare
            l_data_type     text;
            l_data_result   text;
        begin
            l_data_type := pg_typeof(p_data)::text;
            if    l_data_type = 'aclitem'                     then l_data_result := hash_aclitem(p_data);
            elsif l_data_type = 'anyarray'                    then l_data_result := hash_array(p_data);
            elsif l_data_type = 'numeric'                     then l_data_result := hash_numeric(p_data);
            elsif l_data_type = 'anyrange'                    then l_data_result := hash_range(p_data);
            elsif l_data_type = 'character'                   then l_data_result := hashbpchar(p_data);
            elsif l_data_type = '"char"'                      then l_data_result := hashchar(p_data);
            elsif l_data_type = 'anyenum'                     then l_data_result := hashenum(p_data);
            elsif l_data_type = 'real'                        then l_data_result := hashfloat4(p_data);
            elsif l_data_type = 'double precision'            then l_data_result := hashfloat8(p_data);
            elsif l_data_type = 'inet'                        then l_data_result := hashinet(p_data);
            elsif l_data_type = 'tinyint'                     then l_data_result := hashint1(p_data);
            elsif l_data_type = 'smallint'                    then l_data_result := hashint2(p_data);
            elsif l_data_type = 'int2vector'                  then l_data_result := hashint2vector(p_data);
            elsif l_data_type = 'integer'                     then l_data_result := hashint4(p_data);
            elsif l_data_type = 'bigint'                      then l_data_result := hashint8(p_data);
            elsif l_data_type = 'macaddr'                     then l_data_result := hashmacaddr(p_data);
            elsif l_data_type = 'name'                        then l_data_result := hashname(p_data);
            elsif l_data_type = 'oid'                         then l_data_result := hashoid(p_data);
            elsif l_data_type = 'oidvector'                   then l_data_result := hashoidvector(p_data);
            elsif l_data_type = 'text'                        then l_data_result := hashtext(p_data);
            elsif l_data_type = 'internal'                    then l_data_result := hashvarlena(p_data);
            elsif l_data_type = 'interval'                    then l_data_result := interval_hash(p_data);
            elsif l_data_type = 'smalldatetime'               then l_data_result := smalldatetime_hash(p_data);
            elsif l_data_type = 'time without time zone'      then l_data_result := time_hash(p_data);
            elsif l_data_type = 'timestamp without time zone' then l_data_result := timestamp_hash(p_data);
            elsif l_data_type = 'time with time zone'         then l_data_result := timetz_hash(p_data);
            elsif l_data_type = 'uuid'                        then l_data_result := uuid_hash(p_data);
            else
                raise exception 'Unsupported data type [%]', l_data_type;
            end if;

            if p_buckets = 0
            then
                return abs(l_data_result)::bigint;
            else
                return abs(mod(l_data_result + p_seed, p_buckets + 1));
            end if;
        end;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('ora_hash(''abcdAbcdasd''::text)', '0', '>') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- show(text)
    -- show_parameter(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'show(text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.show (p_keyword  text)
        RETURNS setof text
        IMMUTABLE
        AS $$
        with matched_params as (select name
                                     , setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
                                     , short_desc
                                  from pg_settings
                                 where name like '%'||p_keyword||'%'
                               ),
             max_size as (select max(lengthb(name)) as name_len
                               , max(lengthb(setting)) as setting_len
                               , max(lengthb(short_desc)) as desc_len
                            from matched_params
                         )
        select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
          from max_size
         union all
        select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
          from max_size
         union all
        select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
          from matched_params, max_size;
        $$ LANGUAGE sql;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('(select count(*) from (select pg_catalog.show(''log_connection'')))', '2', '>') into l_result;
    -- =========================================================================
    end if;
    if compat_tools.drop_compat_function('function', 'show_parameter(text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.show_parameter (p_keyword  text)
        RETURNS setof text
        IMMUTABLE
        AS $$
        with matched_params as (select name
                                     , setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
                                     , short_desc
                                  from pg_settings
                                 where name like '%'||p_keyword||'%'
                               ),
             max_size as (select max(lengthb(name)) as name_len
                               , max(lengthb(setting)) as setting_len
                               , max(lengthb(short_desc)) as desc_len
                            from matched_params
                         )
        select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
          from max_size
         union all
        select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
          from max_size
         union all
        select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
          from matched_params, max_size;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================

/*
    -- =========================================================================
    -- dump(anynonarray)
    -- 参考： http://blog.itpub.net/6906/viewspace-2653368/
    -- 测试用例：
    --   select dump('红叶'::text);
    --   select dump('Hongye'::text);
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'dump(anynonarray)', '1.0', 'plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.dump(anynonarray)
        RETURNS TEXT
        IMMUTABLE
        AS $$
        DECLARE
            v_hexstr    TEXT;
            v_hexbyte   TEXT;
            v_tmp       TEXT;
            i           INT;
            v_len       INT;
        BEGIN
            SELECT octet_length($1) into v_len;
            v_hexstr := 'Len=' || v_len || ' ';
            v_tmp := ',';
            FOR i in 1..v_len LOOP
                select to_hex(get_byte($1::bytea, i-1)) into v_hexbyte;
                if i = v_len then
                    v_tmp := '';
                end if;
                v_hexstr := v_hexstr || v_hexbyte || v_tmp;
            END LOOP;
            RETURN v_hexstr;
        END;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('(select count(*) from (select show(''log_connection'')))', '2', '>') into l_result;
    -- =========================================================================
*/
    -- =========================================================================
    -- dump(anyelement,numeric,int8,int8)
    -- 参考： https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-function/dump.sql
    -- 测试用例：
    -- select dump(1234);
    -- select dump(1234.5678);
    -- select dump('abcd'::text);
    -- select dump('abcd'::VARCHAR);
    -- select dump('FFFF'::RAW);
    -- select dump('FFFF'::BLOB);
    -- select dump(DATE'2022-01-23');
    -- select dump(current_timestamp);
    -- select dump('abcd'::text,1016);
    -- select dump('abcd'::text,1016,2,2);
    /*
    with t as (select '测试' b from dual)
    select dump(b,1),
       dump(b,0),
       dump(b,null),
       dump(b,9),
       dump(b,7),
       dump(b,10),
       dump(b,11),
       dump(b,1016),
       dump(b,15.8),
       dump(b,17),
       dump(b,-1),
       dump(b,10,2,2),
       dump(b,10,2)
     from t;
    */
    -- =========================================================================
    -- 变更历史：
    --   3.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    --   4.0 : 修复文本类型中带 \ 无法转换的问题
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'dump(anynonarray)', '1.0') into l_result;
    delete from compat_tools.compat_version where object_name = 'pg_catalog.dump(anynonarray)' and object_version = '1.0';
    delete from temp_result where object_name = 'pg_catalog.dump(anynonarray)';
    select compat_tools.drop_compat_function('function', 'dump(anyelement,numeric,int4,int4)', '3.0', 'plpgsql') into l_result;

    if compat_tools.drop_compat_function('function', 'dump(anyelement,numeric,int8,int8)', '4.0', 'plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.dump(anyelement,numeric default 10,int8 default null,int8 default null)
        RETURNS text
        LANGUAGE plpgsql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $function$
                declare
                    v_typsend   text;
                    v_type text;
                    v_bytea     bytea;
                    v_hexstr    TEXT;
                    v_hexbyte   TEXT;
                    v_tmp       TEXT;
                    i           INT;
                    v_len       INT;
                    v_oid       int;
                    v_charset text :='';
                    v_typcategory text;
                begin
                    select typsend,typname,oid ,typcategory
                    into v_typsend,v_type,v_oid ,v_typcategory
                    from pg_type
                    where oid= pg_typeof($1);
                    if v_type='blob' then
                    v_typsend:='rawsend';
                    elsif v_type='unknown' then
                    v_typsend:='textsend';
                    end if;
                    if v_typcategory='S' then
                    v_bytea:=replace($1,'\','\\')::text::bytea;
                    else
                    EXECUTE 'select '||v_typsend||'(:1)' into v_bytea using $1;
                    end if;
                if $3 is not null and $4 is not null then
                v_bytea:=substr(v_bytea,$3::int,$4::int);
                elsif $3 is not null and $4 is null then
                v_bytea:=substr(v_bytea,$3::int);
                end if;
                    SELECT length(v_bytea) into v_len;
                    $2:=floor($2);
                if $2>1000 and v_typcategory='S' then
                select pg_encoding_to_char(encoding) into v_charset
                from pg_database where datname=current_database();
                v_charset:=' CharacterSet='||v_charset;
                $2:=$2-1000;
                end if;
                    v_hexstr := 'Typ='||v_oid||' Len=' || v_len || v_charset||': ';
                    v_tmp := ',';
                if $2>=16 or $2<0 then
                    FOR i in 1..v_len LOOP
                        select to_hex(get_byte(v_bytea, i-1)) into v_hexbyte;
                        if i = v_len then
                            v_tmp := '';
                        end if;
                        v_hexstr := v_hexstr || v_hexbyte || v_tmp;
                    END LOOP;
                elsif  $2<>8 or $2 is null then
                FOR i in 1..v_len LOOP
                        select get_byte(v_bytea, i-1) into v_hexbyte;
                        if i = v_len then
                            v_tmp := '';
                        end if;
                        v_hexstr := v_hexstr || v_hexbyte || v_tmp;
                    END LOOP;
                elsif   $2=8 then
                raise 'unsupport Octal!';
                end if;
                    RETURN v_hexstr;
                END;
                $function$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dump(1234)', '''Typ=23 Len=4: 0,0,4,210''', '=') into l_result;
    select compat_tools.f_unit_test('dump(1234.5678)', '''Typ=1700 Len=12: 0,2,0,0,0,0,0,4,4,210,22,46''', '=') into l_result;
    select compat_tools.f_unit_test('dump(''abcd''::text)', '''Typ=25 Len=4: 97,98,99,100''', '=') into l_result;
    select compat_tools.f_unit_test('dump(''FFFF''::RAW)', '''Typ=86 Len=2: 255,255''', '=') into l_result;
    select compat_tools.f_unit_test('dump(''FFFF''::blob)', '''Typ=88 Len=2: 255,255''', '=') into l_result;
    select compat_tools.f_unit_test('dump(to_timestamp(''2022-01-01'',''yyyy-mm-dd'') )', '''Typ=1114 Len=8: 0,2,119,120,188,193,128,0''', '=') into l_result;
    select compat_tools.f_unit_test('dump(''abcdefg''::text,1016)', '''Typ=25 Len=7 CharacterSet=%: 61,62,63,64,65,66,67''', 'like') into l_result;
    select compat_tools.f_unit_test('dump(''abcdefg''::text,16,2,2)', '''Typ=25 Len=2: 62,63''', '=') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- instrb(text,text,int8,int8)
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'instrb(text,text,int4,int4)', '2.0', 'plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'instrb(text,text,int8,int8)', '2.0', 'plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.instrb( p_source      text
                                                    , p_search      text
                                                    , p_position    int8  default 1
                                                    , p_occurrence  int8  default 1)
        RETURNS int4 IMMUTABLE strict AS $$
        DECLARE
          l_return    int;
        BEGIN
          -- 异常参数识别
          if p_position <= 0
          then
            raise exception 'argument ''%'' is out of range', p_position;
          end if;
          if p_occurrence <= 0
          then
            raise exception 'argument ''%'' is out of range', p_occurrence;
          end if;

          -- 从头开始搜索
          if p_position = 1
          then
            -- 定位搜索串的字符位置，然后从开头截取到字符位置前的所有字符，再将字符转为16进制串，转换后每2位一个字节，所以除以2的正数，即为字节数
            SELECT case char_instr when 0 then 0 else (length(rawtohex(substr(p_source, 1, char_instr - 1))) / 2)::int + 1 end
              INTO l_return
              FROM (select instr(p_source, p_search, p_position::int, p_occurrence::int) as char_instr) as t;
          -- 从指定位置开始搜索
          else
            SELECT case when search_in_pos = 0 then 0
                        else (length(rawtohex(substr( p_source
                                                    , 1
                                                    , search_in_pos - 1 + length(substrb(p_source, 1, p_position::int - 1))  -- 指定位置之后的搜索下标（字符位置），加上指定位置本身的字符数
                                                    )
                                             )  -- rawtohex将字符转换为十六进制，转换后每两位一个字节
                                    ) / 2)::int + 1  -- 将转换后的十六进制串的长度除以2，即得到字节长度
                   end into l_return
              from (select instr( substrb(p_source, p_position::int)  -- substrb截取指定位置之后的子串，instr搜索指定位置之后的字符位置
                                , p_search
                                , 1
                                , p_occurrence::int
                                ) as search_in_pos) as t;
          end if;

          -- 返回结果，没有搜索到内容，则返回0
          if l_return is null
          then
            return 0;
          else
            return l_return;
          end if;
        END;
        $$ LANGUAGE plpgsql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    if l_db_encoding='GBK' then
        select compat_tools.f_unit_test('instrb(''hhh红ongye'', ''on'')', '6') into l_result;
        select compat_tools.f_unit_test('instrb(''hongye'', ''on'')', '2') into l_result;
        select compat_tools.f_unit_test('instrb(''hongye'', ''xx'')', '0') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6)', '6') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6)', '6') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '12') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 1)', '6') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 2)', '12') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 6, 3)', '0') into l_result;
    else
        select compat_tools.f_unit_test('instrb(''hhh红ongye'', ''on'')', '7') into l_result;
        select compat_tools.f_unit_test('instrb(''hongye'', ''on'')', '2') into l_result;
        select compat_tools.f_unit_test('instrb(''hongye'', ''xx'')', '0') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '7') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7)', '7') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 8)', '13') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 1)', '7') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 2)', '13') into l_result;
        select compat_tools.f_unit_test('instrb(''hhh红ongye on xxxx'', ''on'', 7, 3)', '0') into l_result;
    end if;
    -- select instrb('hhh红ongye on xxxx', 'on', 7, 0);  -- Error
    -- =========================================================================


    -- =========================================================================
    -- vsize(anyelement)
    -- =========================================================================
    select count(*) into l_cnt
      from pg_settings
     where name = 'application_name'
       and setting = 'checkMe';

    if l_cnt = 0
    then
        create or replace synonym pg_catalog.vsize for pg_catalog.pg_column_size;

        -- 记录版本信息
        insert into compat_tools.compat_version
        values ('synonym', 'vsize', '1.0')
        ON DUPLICATE KEY UPDATE object_version = '1.0';
    end if;

    -- 在 checkMe 脚本中，如果已经存在同义词，则不记录结果
    select count(*) + (select count(*)
                         from pg_settings
                        where name = 'application_name'
                          and setting = 'checkMe')
      into l_cnt
      from pg_synonym
     where synname = 'vsize'
       and synnamespace = (select oid
                             from pg_namespace
                            where nspname = 'pg_catalog');

    -- 记录当前结果
    if l_cnt < 2
    then
        insert into temp_result
        select 'synonym' as object_type
             , 'vsize' as object_name
             , max(object_version) as local_version
             , '1.0' as script_version
             , '' as local_language
             , '' as script_language
             , 'Create'
          from compat_tools.compat_version
         where object_name = 'vsize'
           and object_type = 'synonym';
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_catalog.vsize(123456789)', 4) into l_result;
    select compat_tools.f_unit_test('pg_catalog.vsize(''hongye'')', 7) into l_result;
    if l_db_encoding='GBK' then
    select compat_tools.f_unit_test('pg_catalog.vsize(''红叶'')', 5) into l_result;
    else
    select compat_tools.f_unit_test('pg_catalog.vsize(''红叶'')', 7) into l_result;
    end if;
    select compat_tools.f_unit_test('pg_catalog.vsize(now())', 8) into l_result;
    select compat_tools.f_unit_test('pg_catalog.vsize(sysdate)', 8) into l_result;
    -- =========================================================================


    -- =========================================================================
    -- tz_offset(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'tz_offset(text)', '1.0', 'plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.tz_offset(text)
        RETURNS text IMMUTABLE strict AS $$
        DECLARE
          l_tz_offset    text;
        BEGIN
          -- 直接输出时间偏移量
          if $1 ~ '^[+-]?\d{2}:\d{2}(:\d{2})?$'
          then
            return $1;
          end if;

          -- 时区名转偏移量
          SELECT max(utc_offset)
            INTO l_tz_offset
            FROM pg_timezone_names
           WHERE name = $1
              OR abbrev = $1;

          -- 返回时区偏移量，未找到则触发异常
          if l_tz_offset is not null
          then
            return l_tz_offset;
          else
            raise exception 'timezone region not found';
          end if;
        END;
        $$ LANGUAGE plpgsql;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_catalog.tz_offset(''EST'')', '''-05:00:00''') into l_result;
    select compat_tools.f_unit_test('pg_catalog.tz_offset(''Asia/Shanghai'')', '''08:00:00''') into l_result;
    select compat_tools.f_unit_test('pg_catalog.tz_offset(''08:00'')', '''08:00''') into l_result;
    select compat_tools.f_unit_test('pg_catalog.tz_offset(''-08:11'')', '''-08:11''') into l_result;
    select compat_tools.f_unit_test('pg_catalog.tz_offset(''+08:11:34'')', '''+08:11:34''') into l_result;
    -- =========================================================================
    end if;
    -- =========================================================================

    -- =========================================================================
    -- sys_context(text,text,int8)
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    --   2.1 : 修改IP_ADDRESS/NETWORK_PROTOCOL的取值方式
    --   2.2 : 修改SESSIONID的取值方式
    -- =========================================================================
    select compat_tools.drop_compat_function('function', 'sys_context(text,text,int4)', '2.1', 'sql') into l_result;
    if compat_tools.drop_compat_function('function', 'sys_context(text,text,int8)', '2.2', 'sql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.sys_context(namespace text, parameter text, length int8 default 256)
        RETURNS text IMMUTABLE strict AS $$
        select substr( case upper(namespace)
                       when 'SYS_SESSION_ROLES' then (select case when count(*) = 0 then 'FALSE' else 'TRUE' end
                                                        from pg_catalog.pg_auth_members
                                                       where roleid = (select oid from pg_roles where rolname = (case when parameter = upper(parameter) then lower(parameter) else parameter end))
                                                         and member = (select oid from pg_roles where rolname = current_user))
                       when 'USERENV' then case upper(parameter)
                                           when 'CLIENT_IDENTIFIER'     then current_setting('application_name')
                                           when 'CLIENT_INFO'           then current_setting('application_name')
                                           when 'CLIENT_PROGRAM_NAME'   then current_setting('application_name')
                                           when 'CDB_NAME'              then current_database()::text
                                           when 'CON_ID'                then '1'
                                           when 'CON_NAME'              then current_database()::text
                                           when 'CURRENT_SCHEMA'        then current_schema()::text
                                           when 'CURRENT_SCHEMAID'      then (select oid from pg_namespace where nspname = current_schema())::text
                                           when 'CURRENT_USER'          then current_user::text
                                           when 'CURRENT_USERID'        then (select oid from pg_roles where rolname = current_user)::text
                                           when 'DATABASE_ROLE'         then (case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end)
                                           when 'DB_NAME'               then current_database()::text
                                           when 'DB_UNIQUE_NAME'        then current_database()::text
                                           when 'HOST'                  then get_hostname()
                                           when 'INSTANCE'              then '1'
                                           when 'INSTANCE_NAME'         then current_setting('pgxc_node_name')
                                           when 'IP_ADDRESS'            then inet_out(inet_client_addr())::text
                                           when 'ISDBA'                 then (select case when rolsystemadmin then 'TRUE' else 'FALSE' end from pg_roles where rolname = current_user)
                                           when 'LANG'                  then (select datcollate from pg_database where datname = current_database())
                                           when 'LANGUAGE'              then (select datctype from pg_database where datname = current_database())
                                           when 'MODULE'                then current_setting('application_name')
                                           when 'NETWORK_PROTOCOL'      then (case when inet_client_addr() is null then 'LOCAL' else 'TCP' end)
                                           when 'NLS_DATE_FORMAT'       then current_setting('nls_timestamp_format')
                                           when 'ORACLE_HOME'           then current_setting('data_directory')
                                           -- when 'OS_USER'
                                           when 'PID'                   then pg_backend_pid()::text
                                           when 'SERVER_HOST'           then current_setting('listen_addresses')
                                           when 'SERVICE_NAME'          then current_database()::text
                                           when 'SESSION_USER'          then current_user::text
                                           when 'SESSION_USERID'        then (select oid from pg_roles where rolname = current_user)::text
                                           when 'SESSIONID'             then (select lwpid  from pg_stat_get_thread() gt where gt.pid=pg_backend_pid())::text
                                           when 'SID'                   then pg_backend_pid()::text
                                           else ''
                                           end
                       else ''
                       end
                     , 1, t.len)
          from (select case when length between 1 and 4000 then length else 256 end as len) as t;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('sys_context(''USERENV'', ''CURRENT_SCHEMA'')', 'current_schema()') into l_result;
    select compat_tools.f_unit_test('length(sys_context(''USERENV'', ''ORACLE_HOME'', 2))', '2') into l_result;
    select compat_tools.f_unit_test('sys_context(''NO_NAMESPACE'', ''NO_PARAMETER'')||''|''', '''|''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- userenv(text)
    -- =========================================================================
    -- 变更历史：
    --   1.1 : 修改SESSIONID的取值方式
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'userenv(text)', '1.1', 'sql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.userenv(parameter text)
        RETURNS text IMMUTABLE strict AS $$
        select case upper(parameter)
               when 'CLIENT_IDENTIFIER'     then current_setting('application_name')
               when 'CLIENT_INFO'           then current_setting('application_name')
               when 'CLIENT_PROGRAM_NAME'   then current_setting('application_name')
               when 'CDB_NAME'              then current_database()::text
               when 'CON_ID'                then '1'
               when 'CON_NAME'              then current_database()::text
               when 'CURRENT_SCHEMA'        then current_schema()::text
               when 'CURRENT_SCHEMAID'      then (select oid from pg_namespace where nspname = current_schema())::text
               when 'CURRENT_USER'          then current_user::text
               when 'CURRENT_USERID'        then (select oid from pg_roles where rolname = current_user)::text
               when 'DATABASE_ROLE'         then (case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end)
               when 'DB_NAME'               then current_database()::text
               when 'DB_UNIQUE_NAME'        then current_database()::text
               when 'HOST'                  then get_hostname()
               when 'INSTANCE'              then '1'
               when 'INSTANCE_NAME'         then current_setting('pgxc_node_name')
               when 'IP_ADDRESS'            then (select remote_ip from comm_client_info() where tid = pg_backend_pid())::text
               when 'ISDBA'                 then (select case when rolsystemadmin then 'TRUE' else 'FALSE' end from pg_roles where rolname = current_user)
               when 'LANG'                  then (select datcollate from pg_database where datname = current_database())
               when 'LANGUAGE'              then (select datctype from pg_database where datname = current_database())
               when 'MODULE'                then current_setting('application_name')
               when 'NETWORK_PROTOCOL'      then (select case when remote_ip = '[local]' then 'LOCAL' else 'TCP' end from comm_client_info() where tid = pg_backend_pid())
               when 'NLS_DATE_FORMAT'       then current_setting('nls_timestamp_format')
               when 'ORACLE_HOME'           then current_setting('data_directory')
               -- when 'OS_USER'
               when 'PID'                   then pg_backend_pid()::text
               when 'SERVER_HOST'           then current_setting('listen_addresses')
               when 'SERVICE_NAME'          then current_database()::text
               when 'SESSION_USER'          then current_user::text
               when 'SESSION_USERID'        then (select oid from pg_roles where rolname = current_user)::text
               when 'SESSIONID'             then (select lwpid  from pg_stat_get_thread() gt where gt.pid=pg_backend_pid())::text
               when 'SID'                   then pg_backend_pid()::text
               else ''
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('userenv(''CURRENT_SCHEMA'')', 'current_schema()') into l_result;
    select compat_tools.f_unit_test('userenv(''NO_PARAMETER'')||''|''', '''|''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- round(timestamptz,text)
    -- todo: 'IYYY', 'IYY', 'IY', 'I' 格式未实现
    -- =========================================================================
    -- break on round_value skip
    -- select round(to_date('20191231121212','yyyymmddhh24miss') + 366 * level, 'SCC') round_value
    --      , to_date('20191231','yyyymmdd') + 366 * level
    --   from dual connect by 1=1 and level < 400
    --  order by 2;
    if compat_tools.drop_compat_function('function', 'round(timestamptz,text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.round(p_date timestamptz, p_fmt text default 'DD')
        RETURNS timestamp without time zone IMMUTABLE
        AS $$
            select (case when upper(p_fmt) in ('DDD', 'DD', 'J') then date_trunc('day', p_date + interval '12 hours')
                         when upper(p_fmt) in ('HH', 'HH12', 'HH24') then date_trunc('hour', p_date + interval '30 minutes')
                         when upper(p_fmt) = 'MI' then date_trunc('minute', p_date + interval '30 seconds')
                         when upper(p_fmt) in ('SYYYY', 'YYYY', 'YEAR', 'SYEAR', 'YYY', 'YY', 'Y') then date_trunc('year', p_date + interval '6 months')
                         when upper(p_fmt) = 'SS' then date_trunc('second', p_date + interval '500000 microseconds')
                         -- 规约到周日（'DAY', 'DY', 'D'）的逻辑如下：
                         --   dow = extract('dow' from p_date) 取一周内的天数，从 0 ~ 6，分别表示 周日 ~ 周六
                         --   以周日为中心，前面取 4 天，后面取 2 天
                         --   1. 周日不变； 0: -0
                         --   2. 周日后面的周一至周二，调整到当前周日： 1: -1, 2: -2
                         --   3. 周日前面的周三至周六，调整到当前周日： 3: +4, 4: +3, 5: +2, 6: +1
                         when upper(p_fmt) in ('DAY', 'DY', 'D') then case when extract('dow' from p_date) <= 2 then date_trunc('day', p_date - interval '1 days' * extract('dow' from p_date))
                                                                           else date_trunc('day', p_date + interval '1 days' * (7 - extract('dow' from p_date)))
                                                                      end
                         -- 规约到周一（'IW'）的逻辑如下：
                         --   dow = extract('dow' from p_date) 取一周内的天数，从 0 ~ 6，分别表示 周日 ~ 周六
                         --   以周一为中心，前面取 4 天，后面取 2 天
                         --   1. 周一不变； 1: -0
                         --   2. 周一后面的周二至周三，调整到当前周一： 2: -1, 3: -2;
                         --   3. 周一前面的周四至周日，调整到当前周一： 4: +4, 5: +3, 6: +2, 7: +1
                         when upper(p_fmt) = 'IW' then case when extract('dow' from p_date) <= 3 then date_trunc('day', p_date - interval '1 days' * (extract('dow' from p_date) - 1))
                                                            else date_trunc('day', p_date + interval '1 days' * (8 - extract('dow' from p_date)))
                                                       end
                         -- 规约到当月1号的星期数（'W'）的逻辑如下：
                         --   day = extract('day' from p_date) 取一个月内的天数
                         --   最终规约结果： 1 = 1~3, 8 = 4~10, 15 = 11 ~ 17, 22: 18 ~ 24, 29: 25 ~ 31
                         --   映射函数： 7 * ceil((day - 3) / 7)
                         when upper(p_fmt) = 'W' then date_trunc('month', p_date) + interval '1 day' * (7 * ceil((extract('day' from p_date) - 3) / 7))
                         -- 规约到当年1号的星期数（'WW'）的逻辑如下：
                         --   doy = extract('doy' from p_date) 取一年内的天数
                         --   最终规约结果： 1 = 1~3, 8 = 4~10, 15 = 11 ~ 17, 22: 18 ~ 24, 29: 25 ~ 31 ...
                         --   映射函数： 7 * ceil((doy - 3) / 7)
                         when upper(p_fmt) = 'WW' then date_trunc('year', p_date) + interval '1 day' * (7 * ceil((extract('doy' from p_date) - 3) / 7))
                         when upper(p_fmt) in ('MONTH', 'MON', 'MM', 'RM') then date_trunc('month', p_date + interval '15 days')
                         when upper(p_fmt) = 'Q' then case when to_char(p_date, 'MMDD') >= '1116' then date_trunc('year', p_date) + interval '1 year'
                                                           when to_char(p_date, 'MMDD') >= '0816' then date_trunc('year', p_date) + interval '9 months'
                                                           when to_char(p_date, 'MMDD') >= '0516' then date_trunc('year', p_date) + interval '6 months'
                                                           when to_char(p_date, 'MMDD') >= '0216' then date_trunc('year', p_date) + interval '3 months'
                                                           else date_trunc('year', p_date)
                                                      end
                         -- when upper(p_fmt) in ('IYYY', 'IYY', 'IY', 'I') then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                         when upper(p_fmt) in ('CC', 'SCC') then date_trunc('century', p_date + interval '49 years')
                         else null end)::timestamp without time zone;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    -- select round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'))  R_DEFAULT_1
    --      , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY') as R_YYYY_1
    --      , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'MM') as R_MM_1
    --      , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'DD') as R_DD_1
    --      , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'HH24') as R_HH24_1
    --      , round(to_timestamp('1999-01-02 01:02:03', 'YYYY-MM-DD HH24:MI:SS'), 'MI') as R_MI_1
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))  R_DEFAULT_2
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY') as R_YYYY_2
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'MM') as R_MM_2
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'DD') as R_DD_2
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'HH24') as R_HH24_2
    --      , round(to_timestamp('2012-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), 'MI') as R_MI_2
    --   FROM DUAL;
    -- Oracle:
    -- R_DEFAULT_1         R_YYYY_1            R_MM_1              R_DD_1              R_HH24_1            R_MI_1
    -- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
    -- R_DEFAULT_2         R_YYYY_2            R_MM_2              R_DD_2              R_HH24_2            R_MI_2
    -- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
    -- 1999-01-02 00:00:00 1999-01-01 00:00:00 1999-01-01 00:00:00 1999-01-02 00:00:00 1999-01-02 01:00:00 1999-01-02 01:02:00
    -- 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00 2013-01-01 00:00:00

    -- openGauss:
    --      r_default_1     |      r_yyyy_1       |       r_mm_1        |       r_dd_1        |      r_hh24_1       |       r_mi_1        |     r_default_2     |      r_yyyy_2       |       r_mm_2        |       r_dd_2        |      r_hh24_2       |       r_mi_2
    -- ---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
    --  1999-01-02 00:00:00 | 1999-01-01 00:00:00 | 1999-01-01 00:00:00 | 1999-01-02 00:00:00 | 1999-01-02 01:00:00 | 1999-01-02 01:02:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00 | 2013-01-01 00:00:00
    -- (1 row)

    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp)::text', '''1999-01-02 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''YYYY'')::text', '''1999-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''MM'')::text', '''1999-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''DD'')::text', '''1999-01-02 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''HH24'')::text', '''1999-01-02 01:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''1999-01-02 01:02:03''::timestamp, ''MI'')::text', '''1999-01-02 01:02:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp)::text', '''2013-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''YYYY'')::text', '''2013-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''MM'')::text', '''2013-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''DD'')::text', '''2013-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''HH24'')::text', '''2013-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('round(''2012-12-31 23:59:59''::timestamp, ''MI'')::text', '''2013-01-01 00:00:00''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- bitor(int8,int8)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bitor.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'bitor(int8,int8)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bitor(int8, int8)
        RETURNS int8 IMMUTABLE STRICT
        LANGUAGE sql
        AS $$ select $1 | $2 $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('bitor(1, 1)', '1') into l_result;
    select compat_tools.f_unit_test('bitor(1, 0)', '1') into l_result;
    select compat_tools.f_unit_test('bitor(12, 6)', '14') into l_result;
    select compat_tools.f_unit_test('bitor(3, 6)', '7') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- bitor(int8,int8)
    -- 参考 ：https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bitxor.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'bitxor(int8,int8)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bitxor(int8, int8)
        RETURNS int8 IMMUTABLE STRICT
        LANGUAGE sql
        AS $$ select $1 # $2 $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('bitxor(1, 1)', '0') into l_result;
    select compat_tools.f_unit_test('bitxor(1, 0)', '1') into l_result;
    select compat_tools.f_unit_test('bitxor(12, 6)', '10') into l_result;
    select compat_tools.f_unit_test('bitxor(3, 6)', '5') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- bit_and_agg(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_and_agg.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'bit_and_agg(numeric)', '1.0', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bit_and_agg_state_func(results int, val numeric)
        RETURNS int
        LANGUAGE sql
        COST 50 IMMUTABLE
        AS $$
        select (case when results is null then val else  results&val end)::int;
        $$;

        CREATE AGGREGATE pg_catalog.bit_and_agg(numeric)
        (
          sfunc = pg_catalog.bit_and_agg_state_func,
          stype = int
        );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- bit_or_agg(numeric)
    -- 参考 https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_or_agg.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'bit_or_agg(numeric)', '1.0', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bit_or_agg_state_func(results int, val numeric)
        RETURNS int
        LANGUAGE sql
        COST 50 IMMUTABLE
        AS $$
        select (case when results is null then val else  results|val end)::int;
        $$;

        CREATE AGGREGATE pg_catalog.bit_or_agg(numeric)
        (
          sfunc = pg_catalog.bit_or_agg_state_func,
          stype = int
        );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- bit_xor_agg(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bit_xor_agg.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'bit_xor_agg(numeric)', '1.0', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bit_xor_agg_state_func(results int, val numeric)
        RETURNS int
        LANGUAGE sql
        COST 50 IMMUTABLE
        AS $$
        select (case when results is null then val else  results # val end)::int;
        $$;

        CREATE AGGREGATE pg_catalog.bit_xor_agg(numeric)
        (
          sfunc = pg_catalog.bit_xor_agg_state_func,
          stype = int
        );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- bin_to_num(int4[])
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/bin_to_num.sql
    -- 2.1.0数据库版本,A兼容模式，array_to_string第二个参数传空值会输出异常，因此暂时指定分隔符再去掉
    -- https://gitee.com/opengauss/openGauss-server/issues/I4SFRR?from=project-issue
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'bin_to_num(int4[])', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.bin_to_num(VARIADIC integer [])
        RETURNS int
        LANGUAGE sql
        NOT FENCED NOT SHIPPABLE
        AS $$
        select int8(replace(array_to_string($1,','),',')::varbit)::int;
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('bin_to_num(1,0,1,0)', '10') into l_result;
    select compat_tools.f_unit_test('bin_to_num(1,1,1,1)', '15') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- any_value(anyelement)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/any_value.sql
    -- 备注 :仅数据库2.1.0及以上版本可用
    -- =========================================================================
    if regexp_substr((string_to_array(regexp_substr(version(), '\([^\)]+\)'),' '))[2],'\d[^\.]*?\.\d[^\.]*?')::NUMERIC>=2.1 then
    if compat_tools.drop_compat_function('aggregate', 'any_value(anyelement)', '1.0', 'internal')
    then
        create aggregate pg_catalog.any_value(anyelement) (
            sfunc = first_transition,
            stype = anyelement
        );
    end if;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- sinh(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/sinh.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'sinh(numeric)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.sinh(numeric)
        returns numeric
        LANGUAGE sql
        as $$
        select (exp($1)-exp(-$1))/2 $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- cosh(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/cosh.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'cosh(numeric)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.cosh(numeric)
        returns numeric
        LANGUAGE sql
        as $$
        select (exp($1)+exp(-$1))/2 $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- tanh(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/tanh.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'tanh(numeric)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.tanh(numeric)
        returns numeric
        LANGUAGE sql
        as $$
        select (exp($1)-exp(-$1))/(exp($1)+exp(-$1)) $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- lnnvl(bool)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/lnnvl.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'lnnvl(bool)', '1.0')
    then
        create or replace function pg_catalog.lnnvl(bool)
        returns BOOl
        LANGUAGE sql
        as $$ select case when $1 is null or $1=false then true else false end $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- numtoyminterval(numeric,text)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/numtoyminterval.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'numtoyminterval(numeric,text)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.numtoyminterval(numeric,text)
        returns interval
        LANGUAGE sql
        as $$
        select NUMTODSINTERVAL($1,$2) $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- systimestamp()
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/systimestamp.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'systimestamp()', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog."systimestamp"()
        RETURNS timestamp with time zone LANGUAGE sql
        AS $$
        select current_timestamp $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- remainder(numeric,numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/remainder.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'remainder(numeric,numeric)', '1.0')
    then
        create or replace function pg_catalog.remainder(numeric,numeric)
        returns numeric
        LANGUAGE sql
        as $$
        select $1-$2*round($1/$2) $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('remainder(5.5,2)', '-0.5') into l_result;
    select compat_tools.f_unit_test('remainder(8,2)', '0') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- round_ties_to_even(numeric,int8)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/round_ties_to_even.sql
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    --移除单参数版本
    select compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric)', '1.0') into l_result;
    delete from compat_tools.compat_version where object_name = 'pg_catalog.round_ties_to_even(numeric)' and object_version = '1.0';
    delete from temp_result where object_name = 'pg_catalog.round_ties_to_even(numeric)';

    select compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric,int4)', '2.0','plpgsql') into l_result;
    if compat_tools.drop_compat_function('function', 'round_ties_to_even(numeric,int8)', '2.0','plpgsql')
    then
        create or replace function pg_catalog.round_ties_to_even(n NUMERIC,places int8 DEFAULT 0)
        RETURNS numeric
        LANGUAGE plpgsql
        IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE COST 1
        AS $$
        declare
        l_ret numeric;
        l_dif numeric;
        begin
        l_ret := round(n,places::int);
        l_dif := l_ret-n;
        if abs(l_dif)*(10^places) = 0.5 then
        if not (l_ret * (10^places)) % 2 = 0 then
        l_ret := round(n-l_dif,places::int);
        end if;
        end if;
        return l_ret;
        end;
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('round_ties_to_even(4.5)', '4') into l_result;
    select compat_tools.f_unit_test('round_ties_to_even(4.6)', '5.0') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- soundex(text)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/soundex.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'soundex(text)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.soundex(text)
        returns TEXT
        LANGUAGE sql
        as $$
        select rpad(regexp_replace(string_agg(x, ''), '(\w)\1{1,}', '\1'),4,'0')
            from (select case
                        when rownum = 1 then
                            UPPER(c)
                        when c in('b', 'f', 'p', 'v') then
                            '1'
                        when c in('c', 'g', 'j', 'k', 'q', 's', 'x', 'z') then
                            '2'
                        when c in('d', 't') then
                            '3'
                        when c in('l') then
                            '4'
                        when c in('m', 'n') then
                            '5'
                        when c in('r') then
                            '6'
                        end x
                    from (select unnest(string_to_array(lower($1), null) ) c ) ) $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('soundex(''smith'')', '''S530''') into l_result;
    select compat_tools.f_unit_test('soundex(''OPENGAUSS'')', '''O152''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- kurtosis_pop(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/kurtosis_pop.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'kurtosis_pop(numeric)', '1.1', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.kurtosis_pop_final_func(results numeric [])
        RETURNS numeric
        LANGUAGE plpgsql
        COST 111 IMMUTABLE
        AS $$
        DECLARE
        av     numeric;
        stp    numeric;
        ct     numeric;
        avsp   numeric;
        kurt_s numeric;
        begin
        select avg(s) :: numeric, power(stddev(s), 4) :: numeric, count(s) :: numeric into av, stp, ct
            from (select unnest(results) s );
        select sum(power(s - av, 4)) into avsp
            from (select unnest(results) s );
        kurt_s := ct * (ct + 1) * avsp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
            3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3));
        return (kurt_s*(ct-2)*(ct-3)/(ct-1)-6)/(ct+1);
        EXCEPTION WHEN OTHERS THEN RETURN 0;
        end; $$;

        CREATE AGGREGATE pg_catalog.kurtosis_pop(numeric)
                (
                sfunc = array_append,
                stype = numeric[],
                FINALFUNC = pg_catalog.kurtosis_pop_final_func
                );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- kurtosis_samp(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/kurtosis_samp.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'kurtosis_samp(numeric)', '1.1', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.kurtosis_samp_final_func(results numeric [])
        RETURNS numeric
        LANGUAGE plpgsql
        COST 111 IMMUTABLE
        AS $$
        DECLARE
        av   numeric;
        stp  numeric;
        ct   numeric;
        avsp numeric;
        begin
        select avg(s) :: numeric, power(stddev(s), 4) :: numeric, count(s) :: numeric into av, stp, ct
            from (select unnest(results) s );
        select sum(power(s - av, 4)) into avsp
            from (select unnest(results) s );
        return ct * (ct + 1) * avsp / ((ct - 1) * (ct - 2) * (ct - 3) * stp) -
            3 * power((ct - 1), 2) / ((ct - 2) * (ct - 3));
            EXCEPTION WHEN OTHERS THEN RETURN 0;
        end; $$;

        CREATE AGGREGATE pg_catalog.kurtosis_samp(numeric)
                (
                sfunc = array_append,
                stype = numeric[],
                FINALFUNC = pg_catalog.kurtosis_samp_final_func
                );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- skewness_pop(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/skewness_pop.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'skewness_pop(numeric)', '1.1', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.skewness_pop_final_func(results numeric [])
        RETURNS numeric
        LANGUAGE plpgsql
        COST 111 IMMUTABLE
        AS $$
        DECLARE
        av   numeric;
        stp  numeric;
        ct   numeric;
        avsp numeric;
        skew_s numeric;
        begin
        select avg(s) :: numeric, power(stddev(s), 3) :: numeric, count(s) :: numeric into av, stp, ct
            from (select unnest(results) s );
        select sum(power(s - av, 3)) into avsp
            from (select unnest(results) s );
        skew_s := ct * (avsp / ((ct - 1) * (ct - 2) * stp));
        return skew_s*(ct-2)/sqrt(ct*(ct-1));
        EXCEPTION WHEN OTHERS THEN RETURN 0;
        end; $$;

        CREATE AGGREGATE pg_catalog.skewness_pop(numeric)
                (
                sfunc = array_append,
                stype = numeric[],
                FINALFUNC = pg_catalog.skewness_pop_final_func
                );

    end if;
    -- =========================================================================

    -- =========================================================================
    -- skewness_samp(numeric)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/skewness_samp.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('aggregate', 'skewness_samp(numeric)', '1.1', 'internal')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.skewness_samp_final_func(results numeric [])
        RETURNS numeric
        LANGUAGE plpgsql
        COST 111 IMMUTABLE
        AS $$
        DECLARE
        av     numeric;
        stp    numeric;
        ct     numeric;
        avsp   numeric;
        begin
        select avg(s) :: numeric, power(stddev(s), 3) :: numeric, count(s) :: numeric into av, stp, ct
            from (select unnest(results) s );
        select sum(power(s - av, 3)) into avsp
            from (select unnest(results) s );
        return ct * (avsp / ((ct - 1) * (ct - 2) * stp));
        EXCEPTION WHEN OTHERS THEN RETURN 0;
        end; $$;

        CREATE AGGREGATE pg_catalog.skewness_samp(numeric)
                (
                sfunc = array_append,
                stype = numeric[],
                FINALFUNC = pg_catalog.skewness_samp_final_func
                );
    end if;
    -- =========================================================================

    -- =========================================================================
    -- asciistr(text)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/asciistr.sql
    -- 2023-07-01 ： 3.0 版本，调整ascii扩展集也转换成2字节
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'asciistr(text)', '3.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.asciistr(text)
        RETURNS text
        LANGUAGE sql
        NOT FENCED NOT SHIPPABLE
        AS $$
        select string_agg( (case when ascii(s)<=127 and s!='\' then s else
        '\'||lpad(upper(to_hex(ascii(s))::text),4,'0') end ),'')
        from (select unnest(string_to_array($1, null) ) s);
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('asciistr(''abc测试?+12*&@u'')', '''abc\6D4B\8BD5?+12*&@u''') into l_result;
    select compat_tools.f_unit_test('asciistr(''AB。CDE'')', '''AB\3002CDE''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- unistr(text)
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/master/oracle-function/unistr.sql
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'unistr(text)', '1.0','plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.unistr(text)
        RETURNS text
        LANGUAGE plpgsql
        NOT FENCED NOT SHIPPABLE
        AS $$
        DECLARE
        r text;
        begin
        IF LENGTH(instr($1,'\'))>0 THEN
        EXECUTE left(REPLACE(REPLACE('select '||'U&'''||quote_nullable($1)||'''','U&''E''','U&'''),'\\','\'),-1) into r;
        ELSe r:=$1;
        END IF;
        return r;
        end; $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('unistr(''\6D4B\8BD5'')', '''测试''') into l_result;
    -- 防注入测试
    select compat_tools.f_unit_test('unistr(''\6D4B\8BD5'''';select ''''1  '')', '''测试'''';select ''''1  ''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- to_blob(raw)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_blob(raw)', '1.0')
    then
        create or replace function pg_catalog.to_blob(raw)
        returns blob
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        SELECT $1::blob;
        $$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- convert(text,text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'convert(text,text,text)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.CONVERT(TEXT,TEXT,TEXT DEFAULT 'utf8')
        RETURNS TEXT
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        SELECT convert_from(convert_to($1,$2),$3);
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('CONVERT(''娴嬭瘯'', ''gbk'', ''utf8'')', '''测试''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- nls_charset_name(int8)
    -- =========================================================================
    -- 变更历史：
    --   2.0 : 参数中的 int4 类型调整为 int8, 适用性更广
    -- =========================================================================
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.0') = 1
    then
    select compat_tools.drop_compat_function('function', 'nls_charset_name(int4)', '2.0') into l_result;
    if compat_tools.drop_compat_function('function', 'nls_charset_name(int8)', '2.0')
    then
        create or replace function pg_catalog.nls_charset_name(int4)
        RETURNS TEXT
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        as $$
        select ('{
        "1":"US7ASCII","2":"WE8DEC","3":"WE8HP","4":"US8PC437","5":"WE8EBCDIC37",
        "6":"WE8EBCDIC500","7":"WE8EBCDIC1140","8":"WE8EBCDIC285","9":"WE8EBCDIC1146","10":"WE8PC850",
        "11":"D7DEC","12":"F7DEC","13":"S7DEC","14":"E7DEC","15":"SF7ASCII",
        "16":"NDK7DEC","17":"I7DEC","18":"NL7DEC","19":"CH7DEC","20":"YUG7ASCII",
        "21":"SF7DEC","22":"TR7DEC","23":"IW7IS960","25":"IN8ISCII","27":"WE8EBCDIC1148",
        "28":"WE8PC858","31":"WE8ISO8859P1","32":"EE8ISO8859P2","33":"SE8ISO8859P3","34":"NEE8ISO8859P4",
        "35":"CL8ISO8859P5","36":"AR8ISO8859P6","37":"EL8ISO8859P7","38":"IW8ISO8859P8","39":"WE8ISO8859P9",
        "40":"NE8ISO8859P10","41":"TH8TISASCII","42":"TH8TISEBCDIC","43":"BN8BSCII","44":"VN8VN3",
        "45":"VN8MSWIN1258","46":"WE8ISO8859P15","47":"BLT8ISO8859P13","48":"CEL8ISO8859P14","49":"CL8ISOIR111",
        "50":"WE8NEXTSTEP","51":"CL8KOI8U","52":"AZ8ISO8859P9E","61":"AR8ASMO708PLUS","70":"AR8EBCDICX",
        "72":"AR8XBASIC","81":"EL8DEC","82":"TR8DEC","90":"WE8EBCDIC37C","91":"WE8EBCDIC500C",
        "92":"IW8EBCDIC424","93":"TR8EBCDIC1026","94":"WE8EBCDIC871","95":"WE8EBCDIC284","96":"WE8EBCDIC1047",
        "97":"WE8EBCDIC1140C","98":"WE8EBCDIC1145","99":"WE8EBCDIC1148C","100":"WE8EBCDIC1047E","101":"WE8EBCDIC924",
        "110":"EEC8EUROASCI","113":"EEC8EUROPA3","114":"LA8PASSPORT","140":"BG8PC437S","150":"EE8PC852",
        "152":"RU8PC866","153":"RU8BESTA","154":"IW8PC1507","155":"RU8PC855","156":"TR8PC857",
        "158":"CL8MACCYRILLIC","159":"CL8MACCYRILLICS","160":"WE8PC860","161":"IS8PC861","162":"EE8MACCES",
        "163":"EE8MACCROATIANS","164":"TR8MACTURKISHS","165":"IS8MACICELANDICS","166":"EL8MACGREEKS","167":"IW8MACHEBREWS",
        "170":"EE8MSWIN1250","171":"CL8MSWIN1251","172":"ET8MSWIN923","173":"BG8MSWIN","174":"EL8MSWIN1253",
        "175":"IW8MSWIN1255","176":"LT8MSWIN921","177":"TR8MSWIN1254","178":"WE8MSWIN1252","179":"BLT8MSWIN1257",
        "180":"D8EBCDIC273","181":"I8EBCDIC280","182":"DK8EBCDIC277","183":"S8EBCDIC278","184":"EE8EBCDIC870",
        "185":"CL8EBCDIC1025","186":"F8EBCDIC297","187":"IW8EBCDIC1086","188":"CL8EBCDIC1025X","189":"D8EBCDIC1141",
        "190":"N8PC865","191":"BLT8CP921","192":"LV8PC1117","193":"LV8PC8LR","194":"BLT8EBCDIC1112",
        "195":"LV8RST104090","196":"CL8KOI8R","197":"BLT8PC775","198":"DK8EBCDIC1142","199":"S8EBCDIC1143",
        "200":"I8EBCDIC1144","201":"F7SIEMENS9780X","202":"E7SIEMENS9780X","203":"S7SIEMENS9780X","204":"DK7SIEMENS9780X",
        "205":"N7SIEMENS9780X","206":"I7SIEMENS9780X","207":"D7SIEMENS9780X","208":"F8EBCDIC1147","210":"WE8GCOS7",
        "211":"EL8GCOS7","221":"US8BS2000","222":"D8BS2000","223":"F8BS2000","224":"E8BS2000",
        "225":"DK8BS2000","226":"S8BS2000","230":"WE8BS2000E","231":"WE8BS2000","232":"EE8BS2000",
        "233":"CE8BS2000","235":"CL8BS2000","239":"WE8BS2000L5","241":"WE8DG","251":"WE8NCR4970",
        "261":"WE8ROMAN8","262":"EE8MACCE","263":"EE8MACCROATIAN","264":"TR8MACTURKISH","265":"IS8MACICELANDIC",
        "266":"EL8MACGREEK","267":"IW8MACHEBREW","277":"US8ICL","278":"WE8ICL","279":"WE8ISOICLUK",
        "301":"EE8EBCDIC870C","311":"EL8EBCDIC875S","312":"TR8EBCDIC1026S","314":"BLT8EBCDIC1112S","315":"IW8EBCDIC424S",
        "316":"EE8EBCDIC870S","317":"CL8EBCDIC1025S","319":"TH8TISEBCDICS","320":"AR8EBCDIC420S","322":"CL8EBCDIC1025C",
        "323":"CL8EBCDIC1025R","324":"EL8EBCDIC875R","325":"CL8EBCDIC1158","326":"CL8EBCDIC1158R","327":"EL8EBCDIC423R",
        "351":"WE8MACROMAN8","352":"WE8MACROMAN8S","353":"TH8MACTHAI","354":"TH8MACTHAIS","368":"HU8CWI2",
        "380":"EL8PC437S","381":"EL8EBCDIC875","382":"EL8PC737","383":"LT8PC772","384":"LT8PC774",
        "385":"EL8PC869","386":"EL8PC851","390":"CDN8PC863","401":"HU8ABMOD","500":"AR8ASMO8X",
        "504":"AR8NAFITHA711T","505":"AR8SAKHR707T","506":"AR8MUSSAD768T","507":"AR8ADOS710T","508":"AR8ADOS720T",
        "509":"AR8APTEC715T","511":"AR8NAFITHA721T","514":"AR8HPARABIC8T","554":"AR8NAFITHA711","555":"AR8SAKHR707",
        "556":"AR8MUSSAD768","557":"AR8ADOS710","558":"AR8ADOS720","559":"AR8APTEC715","560":"AR8MSWIN1256",
        "561":"AR8NAFITHA721","563":"AR8SAKHR706","565":"AR8ARABICMAC","566":"AR8ARABICMACS","567":"AR8ARABICMACT",
        "590":"LA8ISO6937","798":"WE8DECTST","829":"JA16VMS","830":"JA16EUC","831":"JA16EUCYEN",
        "832":"JA16SJIS","833":"JA16DBCS","834":"JA16SJISYEN","835":"JA16EBCDIC930","836":"JA16MACSJIS",
        "837":"JA16EUCTILDE","838":"JA16SJISTILDE","840":"KO16KSC5601","842":"KO16DBCS","845":"KO16KSCCS",
        "846":"KO16MSWIN949","850":"ZHS16CGB231280","851":"ZHS16MACCGB231280","852":"ZHS16GBK","853":"ZHS16DBCS",
        "854":"ZHS32GB18030","860":"ZHT32EUC","861":"ZHT32SOPS","862":"ZHT16DBT","863":"ZHT32TRIS",
        "864":"ZHT16DBCS","865":"ZHT16BIG5","866":"ZHT16CCDC","867":"ZHT16MSWIN950","868":"ZHT16HKSCS",
        "870":"AL24UTFFSS","871":"UTF8","872":"UTFE","873":"AL32UTF8","992":"ZHT16HKSCS31",
        "993":"ZHT32EUCTST","994":"WE16DECTST2","995":"WE16DECTST","996":"KO16TSTSET","997":"JA16TSTSET2",
        "998":"JA16TSTSET","1000":"UTF16","1001":"US16TSTFIXED","1002":"TIMESTEN8","1830":"JA16EUCFIXED",
        "1832":"JA16SJISFIXED","1833":"JA16DBCSFIXED","1840":"KO16KSC5601FIXED","1842":"KO16DBCSFIXED","1850":"ZHS16CGB231280FIXED",
        "1852":"ZHS16GBKFIXED","1853":"ZHS16DBCSFIXED","1860":"ZHT32EUCFIXED","1863":"ZHT32TRISFIXED","1864":"ZHT16DBCSFIXED",
        "1865":"ZHT16BIG5FIXED","2000":"AL16UTF16","2002":"AL16UTF16LE"
        }'::json->>$1::text)::text;
        $$;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_catalog.nls_charset_name(873)', '''AL32UTF8''') into l_result;
    -- =========================================================================
    end if;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- nls_charset_id(text)
    -- =========================================================================
    if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.0') = 1
    then
    if compat_tools.drop_compat_function('function', 'nls_charset_id(text)', '1.0')
    then
        create or replace function pg_catalog.nls_charset_id(text)
        RETURNS integer
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        select ('{
        "US7ASCII":1,"WE8DEC":2,"WE8HP":3,"US8PC437":4,"WE8EBCDIC37":5,
        "WE8EBCDIC500":6,"WE8EBCDIC1140":7,"WE8EBCDIC285":8,"WE8EBCDIC1146":9,"WE8PC850":10,
        "D7DEC":11,"F7DEC":12,"S7DEC":13,"E7DEC":14,"SF7ASCII":15,
        "NDK7DEC":16,"I7DEC":17,"NL7DEC":18,"CH7DEC":19,"YUG7ASCII":20,
        "SF7DEC":21,"TR7DEC":22,"IW7IS960":23,"IN8ISCII":25,"WE8EBCDIC1148":27,
        "WE8PC858":28,"WE8ISO8859P1":31,"EE8ISO8859P2":32,"SE8ISO8859P3":33,"NEE8ISO8859P4":34,
        "CL8ISO8859P5":35,"AR8ISO8859P6":36,"EL8ISO8859P7":37,"IW8ISO8859P8":38,"WE8ISO8859P9":39,
        "NE8ISO8859P10":40,"TH8TISASCII":41,"TH8TISEBCDIC":42,"BN8BSCII":43,"VN8VN3":44,
        "VN8MSWIN1258":45,"WE8ISO8859P15":46,"BLT8ISO8859P13":47,"CEL8ISO8859P14":48,"CL8ISOIR111":49,
        "WE8NEXTSTEP":50,"CL8KOI8U":51,"AZ8ISO8859P9E":52,"AR8ASMO708PLUS":61,"AR8EBCDICX":70,
        "AR8XBASIC":72,"EL8DEC":81,"TR8DEC":82,"WE8EBCDIC37C":90,"WE8EBCDIC500C":91,
        "IW8EBCDIC424":92,"TR8EBCDIC1026":93,"WE8EBCDIC871":94,"WE8EBCDIC284":95,"WE8EBCDIC1047":96,
        "WE8EBCDIC1140C":97,"WE8EBCDIC1145":98,"WE8EBCDIC1148C":99,"WE8EBCDIC1047E":100,"WE8EBCDIC924":101,
        "EEC8EUROASCI":110,"EEC8EUROPA3":113,"LA8PASSPORT":114,"BG8PC437S":140,"EE8PC852":150,
        "RU8PC866":152,"RU8BESTA":153,"IW8PC1507":154,"RU8PC855":155,"TR8PC857":156,
        "CL8MACCYRILLIC":158,"CL8MACCYRILLICS":159,"WE8PC860":160,"IS8PC861":161,"EE8MACCES":162,
        "EE8MACCROATIANS":163,"TR8MACTURKISHS":164,"IS8MACICELANDICS":165,"EL8MACGREEKS":166,"IW8MACHEBREWS":167,
        "EE8MSWIN1250":170,"CL8MSWIN1251":171,"ET8MSWIN923":172,"BG8MSWIN":173,"EL8MSWIN1253":174,
        "IW8MSWIN1255":175,"LT8MSWIN921":176,"TR8MSWIN1254":177,"WE8MSWIN1252":178,"BLT8MSWIN1257":179,
        "D8EBCDIC273":180,"I8EBCDIC280":181,"DK8EBCDIC277":182,"S8EBCDIC278":183,"EE8EBCDIC870":184,
        "CL8EBCDIC1025":185,"F8EBCDIC297":186,"IW8EBCDIC1086":187,"CL8EBCDIC1025X":188,"D8EBCDIC1141":189,
        "N8PC865":190,"BLT8CP921":191,"LV8PC1117":192,"LV8PC8LR":193,"BLT8EBCDIC1112":194,
        "LV8RST104090":195,"CL8KOI8R":196,"BLT8PC775":197,"DK8EBCDIC1142":198,"S8EBCDIC1143":199,
        "I8EBCDIC1144":200,"F7SIEMENS9780X":201,"E7SIEMENS9780X":202,"S7SIEMENS9780X":203,"DK7SIEMENS9780X":204,
        "N7SIEMENS9780X":205,"I7SIEMENS9780X":206,"D7SIEMENS9780X":207,"F8EBCDIC1147":208,"WE8GCOS7":210,
        "EL8GCOS7":211,"US8BS2000":221,"D8BS2000":222,"F8BS2000":223,"E8BS2000":224,
        "DK8BS2000":225,"S8BS2000":226,"WE8BS2000E":230,"WE8BS2000":231,"EE8BS2000":232,
        "CE8BS2000":233,"CL8BS2000":235,"WE8BS2000L5":239,"WE8DG":241,"WE8NCR4970":251,
        "WE8ROMAN8":261,"EE8MACCE":262,"EE8MACCROATIAN":263,"TR8MACTURKISH":264,"IS8MACICELANDIC":265,
        "EL8MACGREEK":266,"IW8MACHEBREW":267,"US8ICL":277,"WE8ICL":278,"WE8ISOICLUK":279,
        "EE8EBCDIC870C":301,"EL8EBCDIC875S":311,"TR8EBCDIC1026S":312,"BLT8EBCDIC1112S":314,"IW8EBCDIC424S":315,
        "EE8EBCDIC870S":316,"CL8EBCDIC1025S":317,"TH8TISEBCDICS":319,"AR8EBCDIC420S":320,"CL8EBCDIC1025C":322,
        "CL8EBCDIC1025R":323,"EL8EBCDIC875R":324,"CL8EBCDIC1158":325,"CL8EBCDIC1158R":326,"EL8EBCDIC423R":327,
        "WE8MACROMAN8":351,"WE8MACROMAN8S":352,"TH8MACTHAI":353,"TH8MACTHAIS":354,"HU8CWI2":368,
        "EL8PC437S":380,"EL8EBCDIC875":381,"EL8PC737":382,"LT8PC772":383,"LT8PC774":384,
        "EL8PC869":385,"EL8PC851":386,"CDN8PC863":390,"HU8ABMOD":401,"AR8ASMO8X":500,
        "AR8NAFITHA711T":504,"AR8SAKHR707T":505,"AR8MUSSAD768T":506,"AR8ADOS710T":507,"AR8ADOS720T":508,
        "AR8APTEC715T":509,"AR8NAFITHA721T":511,"AR8HPARABIC8T":514,"AR8NAFITHA711":554,"AR8SAKHR707":555,
        "AR8MUSSAD768":556,"AR8ADOS710":557,"AR8ADOS720":558,"AR8APTEC715":559,"AR8MSWIN1256":560,
        "AR8NAFITHA721":561,"AR8SAKHR706":563,"AR8ARABICMAC":565,"AR8ARABICMACS":566,"AR8ARABICMACT":567,
        "LA8ISO6937":590,"WE8DECTST":798,"JA16VMS":829,"JA16EUC":830,"JA16EUCYEN":831,
        "JA16SJIS":832,"JA16DBCS":833,"JA16SJISYEN":834,"JA16EBCDIC930":835,"JA16MACSJIS":836,
        "JA16EUCTILDE":837,"JA16SJISTILDE":838,"KO16KSC5601":840,"KO16DBCS":842,"KO16KSCCS":845,
        "KO16MSWIN949":846,"ZHS16CGB231280":850,"ZHS16MACCGB231280":851,"ZHS16GBK":852,"ZHS16DBCS":853,
        "ZHS32GB18030":854,"ZHT32EUC":860,"ZHT32SOPS":861,"ZHT16DBT":862,"ZHT32TRIS":863,
        "ZHT16DBCS":864,"ZHT16BIG5":865,"ZHT16CCDC":866,"ZHT16MSWIN950":867,"ZHT16HKSCS":868,
        "AL24UTFFSS":870,"UTF8":871,"UTFE":872,"AL32UTF8":873,"ZHT16HKSCS31":992,
        "ZHT32EUCTST":993,"WE16DECTST2":994,"WE16DECTST":995,"KO16TSTSET":996,"JA16TSTSET2":997,
        "JA16TSTSET":998,"UTF16":1000,"US16TSTFIXED":1001,"TIMESTEN8":1002,"JA16EUCFIXED":1830,
        "JA16SJISFIXED":1832,"JA16DBCSFIXED":1833,"KO16KSC5601FIXED":1840,"KO16DBCSFIXED":1842,"ZHS16CGB231280FIXED":1850,
        "ZHS16GBKFIXED":1852,"ZHS16DBCSFIXED":1853,"ZHT32EUCFIXED":1860,"ZHT32TRISFIXED":1863,"ZHT16DBCSFIXED":1864,
        "ZHT16BIG5FIXED":1865,"AL16UTF16":2000,"AL16UTF16LE":2002
        }'::json->>$1)::int;
        $$;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_catalog.nls_charset_id(''AL32UTF8'')', '873') into l_result;
    -- =========================================================================
    end if;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- empty_clob()
    -- 1.1 版本 可返回0长度字符串
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'empty_clob()', '1.2')
    then
        create or replace function pg_catalog.empty_clob() returns clob
        IMMUTABLE STRICT
        language sql
        as $$select encode('\x'::bytea,'hex')::clob;$$;
    end if;
    -- =========================================================================

    -- =========================================================================
    -- sys_extract_utc(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'sys_extract_utc(timestamptz)', '1.0')
    then
        create function pg_catalog.sys_extract_utc(timestamptz) returns timestamp
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        select  $1 at time zone 'UTC';
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('sys_extract_utc(current_timestamp) at time zone ''UTC''', 'current_timestamp') into l_result;
    select compat_tools.f_unit_test('((sys_extract_utc(current_timestamp::TIMESTAMP) at time zone ''UTC'')-current_timestamp)::numeric', '0','=') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- new_time(timestamp,text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'new_time(timestamp,text,text)', '1.0')
    then
        create function pg_catalog.new_time(timestamp,text,text) returns timestamp
        LANGUAGE sql
        IMMUTABLE NOT FENCED NOT SHIPPABLE
        AS $$
        select  $1 at time zone $2 at time zone $3;
        $$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_char(NEW_TIME(TO_DATE(''11-10-09 01:23:45'', ''MM-DD-YY HH24:MI:SS''), ''AST'', ''PST''),''yyyy-mm-dd hh24:mi:ss'')', '''2009-11-09 21:23:45''','=','A') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- lower(anyelement)
    -- lower(bpchar)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'lower(anyelement)', '1.0')
    then
        create or replace function pg_catalog.lower(anyelement) returns text IMMUTABLE STRICT as $$ select pg_catalog.lower($1::text) $$ language sql;
    end if;
    if compat_tools.drop_compat_function('function', 'lower(bpchar)', '1.0')
    then
        create or replace function pg_catalog.lower(bpchar) returns bpchar IMMUTABLE STRICT as $$ select pg_catalog.lower($1::text)::bpchar $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('lower(''Test'')', '''test''') into l_result;
    select compat_tools.f_unit_test('lower(''test''::text)', '''test''') into l_result;
    select compat_tools.f_unit_test('lower(''TesT''::cstring)', '''test''') into l_result;
    select compat_tools.f_unit_test('lower(''TesT''::bpchar(10))=''test''::bpchar(6)', '''true''') into l_result;
    select compat_tools.f_unit_test('lower(''TesT''::bpchar(10))=lower(''test''::bpchar(6))', '''true''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- upper(bpchar)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'upper(bpchar)', '1.0')
    then
        create or replace function pg_catalog.upper(bpchar) returns bpchar IMMUTABLE STRICT as $$ select pg_catalog.upper($1::text)::bpchar $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('upper(''TesT''::bpchar(10))=''TEST''::bpchar(6)', '''true''') into l_result;
    select compat_tools.f_unit_test('upper(''TesT''::bpchar(10))=upper(''TEST''::bpchar(6))', '''true''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- substr(anyelement,int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'substr(anyelement,int8)', '1.0')
    then
        create or replace function pg_catalog.substr(anyelement,int8) returns text IMMUTABLE STRICT as $$ select pg_catalog.substr($1::text, $2::int) $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('substr(''Test_str'', 2)', '''est_str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::text, 4)', '''t_str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6)', '''str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str'', 2::bigint)', '''est_str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::text, 4::bigint)', '''t_str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6::bigint)', '''str''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 2.2)', '''est_str''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- substr(anyelement,int8,int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'substr(anyelement,int8,int8)', '1.0')
    then
        create or replace function pg_catalog.substr(anyelement,int8,int8) returns text IMMUTABLE STRICT as $$ select pg_catalog.substr($1::text, $2::int, $3::int) $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('substr(''Test_str'', 2, 6)', '''est_st''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::text, 4, 2)', '''t_''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6, 1)', '''s''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str'', 2::bigint, 6)', '''est_st''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::text, 4, 2::bigint)', '''t_''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 6::bigint, 1::bigint)', '''s''') into l_result;
    select compat_tools.f_unit_test('substr(''Test_str''::cstring, 2.2, 2.2::bigint)', '''es''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- add_months(timestamp,int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'add_months(timestamp,int8)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.add_months (timestamp, bigint)
        RETURNS timestamp
        LANGUAGE sql
        IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
        AS $function$ select pg_catalog.add_months($1, $2::int) $function$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamp, 1)::text', '''2000-02-29 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamp, 3)::text', '''2004-04-30 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamp, 1)::text', '''2005-02-28 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamp, 14)::text', '''2006-03-03 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamp, 1::bigint)::text', '''2000-02-29 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamp, 3::bigint)::text', '''2004-04-30 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamp, 1::bigint)::text', '''2005-02-28 00:00:00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamp, 14::bigint)::text', '''2006-03-03 00:00:00''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- add_months(timestamptz,int8)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'add_months(timestamptz,int8)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.add_months (timestamptz, bigint)
        RETURNS timestamptz
        LANGUAGE sql
        IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
        AS $function$ select pg_catalog.add_months($1::timestamp, $2::int)::timestamptz $function$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamptz, 1)::text', '''2000-02-29 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamptz, 3)::text', '''2004-04-30 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamptz, 1)::text', '''2005-02-28 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamptz, 14)::text', '''2006-03-03 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2000-01-31''::timestamptz, 1::bigint)::text', '''2000-02-29 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2004-01-31''::timestamptz, 3::bigint)::text', '''2004-04-30 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-31''::timestamptz, 1::bigint)::text', '''2005-02-28 00:00:00+00''') into l_result;
    select compat_tools.f_unit_test('add_months(''2005-01-03''::timestamptz, 14::bigint)::text', '''2006-03-03 00:00:00+00''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- to_timestamp(text,text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_timestamp(text,text,text)', '1.0')
    then
        create function pg_catalog.to_timestamp(text,text,text) returns timestamp as $$ select pg_catalog.to_timestamp($1::text, $2::text) $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_timestamp(''2000-01-31'', ''YYYY-MM-DD'')::text', '''2000-01-31 00:00:00''') into l_result;
    select compat_tools.f_unit_test('to_timestamp(''2000-01-31'', ''YYYY-MM-DD'', ''NLS_LANG=AMERICAN_AMERICA'')::text', '''2000-01-31 00:00:00''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- rawtohex(anyelement)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'rawtohex(anyelement)', '1.0')
    then
        create function pg_catalog.rawtohex(anyelement) returns text as $$ select pg_catalog.rawtohex($1::text) $$ language sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('rawtohex(''HongyeDBA'')', '''486f6e677965444241''') into l_result;
    select compat_tools.f_unit_test('rawtohex(''HongyeDBA''::cstring)', '''486f6e677965444241''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- to_char(timestamp without time zone,text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_char(timestamp without time zone,text,text)', '1.0')
    then
        create or replace function pg_catalog.to_char(timestamp without time zone, text, text)
        RETURNS text
        LANGUAGE SQL
        STABLE STRICT NOT FENCED SHIPPABLE
        as $function$ select to_char($1, $2) $function$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_char(''2000-01-31 12:12:12''::timestamp, ''yyyy-mm-dd'')', '''2000-01-31''') into l_result;
    select compat_tools.f_unit_test('to_char(''2000-12-12''::timestamp, ''yyyy-mm-dd'', ''NLS_DATE_LANGUAGE=AMERICAN'')', '''2000-12-12''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- to_char(timestamptz,text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_char(timestamptz,text,text)', '1.0')
    then
        create or replace function pg_catalog.to_char(timestamptz, text, text)
        RETURNS text
        LANGUAGE SQL
        STABLE STRICT NOT FENCED SHIPPABLE
        as $function$ select to_char($1, $2) $function$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_char(''2000-01-31 12:12:12''::timestamptz, ''yyyy-mm-dd'')', '''2000-01-31''') into l_result;
    select compat_tools.f_unit_test('to_char(''2000-12-12''::timestamptz, ''yyyy-mm-dd'', ''NLS_DATE_LANGUAGE=AMERICAN'')', '''2000-12-12''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- to_lob(text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'to_lob(text)', '1.0')
    then
        create or replace function pg_catalog.to_lob(text) returns clob
        IMMUTABLE STRICT
        language sql
        as $$select $1::clob;$$;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('to_lob(''abcd'')', '''abcd''') into l_result;
    -- =========================================================================

end;
$VIEW_CREATION$ language plpgsql;



-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name      text  := current_setting('application_name');
    l_failed_cnt    bigint;
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Summary: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   | result_type | case_count | start_time          | complete_time       |'
                           union all
                          select '   |-------------|------------|---------------------|---------------------|'
                           union all
                          select '   | '
                              || rpad((case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end)::text, 11)
                              || ' | '
                              || lpad(count(*)::text, 10)
                              || ' | '
                              || to_char(min(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' | '
                              || to_char(max(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' |' as result_data
                            from compat_tools.compat_testing
                           group by case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Detail (Failed or Null): ';
        raise notice '-- =====================================================================';
        select count(*) into l_failed_cnt
          from compat_tools.compat_testing
         where test_ok is null or (not test_ok);
        if l_failed_cnt = 0
        then
            raise notice '-- <<< ALL SUCCEED >>>';
        else
            for l_app_name in select '   | test_expression                              | result          | expect          | complete_time       |'
                               union all
                              select '   |----------------------------------------------|-----------------|-----------------|---------------------|'
                               union all
                              select '   | '
                                  || case when length(test_expr) > 44 then substr(test_expr, 1, 40)||' ...' else rpad(test_expr, 44) end
                                  || ' | '
                                  || lpad(coalesce(test_result, ' '), 15)
                                  || ' | '
                                  || rpad(coalesce(expect_result, ' '), 15)
                                  || ' | '
                                  || to_char(test_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                                  || ' |' as result_data
                                from compat_tools.compat_testing
                               where test_ok is null or (not test_ok)
            loop
                raise notice '%', l_app_name;
            end loop;
        end if;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

reset behavior_compat_options;

\q
